Script for Date Dimension

 let

    // configurations start

    Today=Date.From(DateTime.LocalNow()), // today's date

    FromYear = 2018, // set the start year of the date dimension. dates start from 1st of January of this year

    ToYear=2021, // set the end year of the date dimension. dates end at 31st of December of this year

    StartofFiscalYear=7, // set the month number that is start of the financial year. example; if fiscal year start is July, value is 7

    firstDayofWeek=Day.Monday, // set the week's start day, values: Day.Monday, Day.Sunday....

    // configuration end

    FromDate=#date(FromYear,1,1),

    ToDate=#date(ToYear,12,31),

    Source=List.Dates(

        FromDate,

        Duration.Days(ToDate-FromDate)+1,

        #duration(1,0,0,0)

    ),

    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),

    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),

    #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type),

    #"Inserted Start of Year" = Table.AddColumn(#"Inserted Year", "Start of Year", each Date.StartOfYear([Date]), type date),

    #"Inserted End of Year" = Table.AddColumn(#"Inserted Start of Year", "End of Year", each Date.EndOfYear([Date]), type date),

    #"Inserted Month" = Table.AddColumn(#"Inserted End of Year", "Month", each Date.Month([Date]), Int64.Type),

    #"Inserted Start of Month" = Table.AddColumn(#"Inserted Month", "Start of Month", each Date.StartOfMonth([Date]), type date),

    #"Inserted End of Month" = Table.AddColumn(#"Inserted Start of Month", "End of Month", each Date.EndOfMonth([Date]), type date),

    #"Inserted Days in Month" = Table.AddColumn(#"Inserted End of Month", "Days in Month", each Date.DaysInMonth([Date]), Int64.Type),

    #"Inserted Day" = Table.AddColumn(#"Inserted Days in Month", "Day", each Date.Day([Date]), Int64.Type),

    #"Inserted Day Name" = Table.AddColumn(#"Inserted Day", "Day Name", each Date.DayOfWeekName([Date]), type text),

    #"Inserted Day of Week" = Table.AddColumn(#"Inserted Day Name", "Day of Week", each Date.DayOfWeek([Date],firstDayofWeek), Int64.Type),

    #"Inserted Day of Year" = Table.AddColumn(#"Inserted Day of Week", "Day of Year", each Date.DayOfYear([Date]), Int64.Type),

    #"Inserted Month Name" = Table.AddColumn(#"Inserted Day of Year", "Month Name", each Date.MonthName([Date]), type text),

    #"Inserted Quarter" = Table.AddColumn(#"Inserted Month Name", "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),

    #"Inserted Start of Quarter" = Table.AddColumn(#"Inserted Quarter", "Start of Quarter", each Date.StartOfQuarter([Date]), type date),

    #"Inserted End of Quarter" = Table.AddColumn(#"Inserted Start of Quarter", "End of Quarter", each Date.EndOfQuarter([Date]), type date),

    #"Inserted Week of Year" = Table.AddColumn(#"Inserted End of Quarter", "Week of Year", each Date.WeekOfYear([Date],firstDayofWeek), Int64.Type),

    #"Inserted Week of Month" = Table.AddColumn(#"Inserted Week of Year", "Week of Month", each Date.WeekOfMonth([Date],firstDayofWeek), Int64.Type),

    #"Inserted Start of Week" = Table.AddColumn(#"Inserted Week of Month", "Start of Week", each Date.StartOfWeek([Date],firstDayofWeek), type date),

    #"Inserted End of Week" = Table.AddColumn(#"Inserted Start of Week", "End of Week", each Date.EndOfWeek([Date],firstDayofWeek), type date),

    FiscalMonthBaseIndex=13-StartofFiscalYear,

    adjustedFiscalMonthBaseIndex=if(FiscalMonthBaseIndex>=12 or FiscalMonthBaseIndex<0) then 0 else FiscalMonthBaseIndex,

    #"Added Custom" = Table.AddColumn(#"Inserted End of Week", "FiscalBaseDate", each Date.AddMonths([Date],adjustedFiscalMonthBaseIndex)),

    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"FiscalBaseDate", type date}}),

    #"Inserted Year1" = Table.AddColumn(#"Changed Type1", "Year.1", each Date.Year([FiscalBaseDate]), Int64.Type),

    #"Renamed Columns1" = Table.RenameColumns(#"Inserted Year1",{{"Year.1", "Fiscal Year"}}),

    #"Inserted Quarter1" = Table.AddColumn(#"Renamed Columns1", "Quarter.1", each Date.QuarterOfYear([FiscalBaseDate]), Int64.Type),

    #"Renamed Columns2" = Table.RenameColumns(#"Inserted Quarter1",{{"Quarter.1", "Fiscal Quarter"}}),

    #"Inserted Month1" = Table.AddColumn(#"Renamed Columns2", "Month.1", each Date.Month([FiscalBaseDate]), Int64.Type),

    #"Renamed Columns3" = Table.RenameColumns(#"Inserted Month1",{{"Month.1", "Fiscal Month"}}),

    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns3",{"FiscalBaseDate"}),

    #"Inserted Age" = Table.AddColumn(#"Removed Columns", "Age", each [Date]-Today, type duration),

    #"Extracted Days" = Table.TransformColumns(#"Inserted Age",{{"Age", Duration.Days, Int64.Type}}),

    #"Renamed Columns4" = Table.RenameColumns(#"Extracted Days",{{"Age", "Day Offset"}}),

    #"Added Custom1" = Table.AddColumn(#"Renamed Columns4", "Month Offset", each (([Year]-Date.Year(Today))*12)

+([Month]-Date.Month(Today))),

    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"Month Offset", Int64.Type}}),

    #"Added Custom2" = Table.AddColumn(#"Changed Type2", "Year Offset", each [Year]-Date.Year(Today)),

    #"Changed Type3" = Table.TransformColumnTypes(#"Added Custom2",{{"Year Offset", Int64.Type}}),

    #"Added Custom3" = Table.AddColumn(#"Changed Type3", "Quarter Offset", each (([Year]-Date.Year(Today))*4)

+([Quarter]-Date.QuarterOfYear(Today))),

    #"Changed Type4" = Table.TransformColumnTypes(#"Added Custom3",{{"Quarter Offset", Int64.Type}}),

    #"Added Custom4" = Table.AddColumn(#"Changed Type4", "Year-Month", each Date.ToText([Date],"MMM yyyy")),

    #"Added Custom5" = Table.AddColumn(#"Added Custom4", "Year-Month Code", each Date.ToText([Date],"yyyyMM")),

    #"Changed Type5" = Table.TransformColumnTypes(#"Added Custom5",{{"Year-Month", type text}, {"Year-Month Code", Int64.Type}})

in

    #"Changed Type5"

Calculate Max of multiple measures in Power BI

In this post I'll show you how to calculate the maximum of multiple measures in Power BI.

First you need to create the several measures or variable within a measures. 

Measure1 = Sum ( ProductA[Stock] )

Measure2 = Sum ( ProductB[Stock] )

Measure3 = ...

After all your measures are created, create a new one which will contain a list of all measures that you want to evaluate

Measure = 
VAR _List =  List { Measure1, Measure2, Measure3,... }

RETURN

MAXX ( _List, [Value] )

This will return the Max value from all measures included in the _List variable.

Quick SQL Cheatsheet

I found the excellent GitHub repository with all relevant SQL queries and some examples on how to use them. Really useful as a side note to all those who are learning SQL.

https://github.com/enochtangg/quick-SQL-cheatsheet/

Make a good use of it!

How to Find Excel Phantom Links - Part 2

Some time ago I've written about some techniques to eliminate Phantom Links in Excel.
Well, the steps described in my previous article - Here - had always solved my problems... until 2 days ago!

While I was updating a workbook, I had to change a database source from 2016 to 2017 (different Excel file but with same structure), so I just clicked the Edit Links button and that Change Source. As soon as Excel had changed all the links to the 2017 database file, I unexpectedly realized that a link to the 2016 database file was still showing in the Edit Links dialog box!
I've tried every single tactic mentioned on my previous article but I just couldn't find and kill the "Phantom link". I was exchanging some thoughts with a colleague who mentioned a couple more tactics that could solve the problem.

• Search for formulas referencing external workbooks:
  Right-click on any sheet tab >> left-click Select All Sheets >> press Ctrl+F and in the Find What field enter  =[    or  !
  
  This will look through all sheets for formulas referencing external workbooks. You have assure that there are no hidden sheets.

• Conditional formatting formulas:
  Go in to Conditional Formatting >> Manage Rules  >> Show Formatting Rule for: this worksheet and remove the rules which refer to the external link

How to create an interactive chart in Excel

Every advanced Excel user knows that a great report has to be as dynamic as possible in order to avoid many manual changes every time you want to see or add new information to your report.
The following video is a  great tutorial of how to create an interactive chart in Excel.

Enjoy and share!

Excel Tip: Enter the same data in multiple cells at once

How to enter the same data into multiple cells of your worksheet at once ?

If you want to enter the same values into multiple cells of your worksheet at once, you can enter it into one and copy it into each of the other cells, however a quicker solution allows you to enter the same thing into all of those cells at once.

Just take the following steps:

• Highlight all of the relevant cells (you can use the mouse to highlight a range, or click on each of the individual cells while holding down the Ctrl key)
• While they are all highlighted, type what you want to enter in all cells and...
• Instead of hitting Enter, hit Ctrl + Enter
• The information will now appear in all of the selected cells.

That's it.


Do you like this free website? Please share this page on Google+ or Facebook.

Retrieve the value from every Nth row in Excel

Imagine that you want to retrieve the values from every 5th row in a certain range of your Excel worksheet.


In case your range is placed on column A:

1. Edit the first cell on the in the column where you want to have the list of every 5th row of column A
2. Write the following formula.
   
   =IF(ROW(A1)*5-5>ROWS($A$4:$A$50),"",INDEX(A$4:A$50,ROW(A1)*5-5))
    
3. Copy the formula all the way down to as many cells as your range rows divided by 5.
4. There you have it.

Note: This example shows you how to retrieve values from every 5th row in Excel but you can do this for any number. To do so just replace the 5-5 by the number you want.

Now let's break down the formula:

The formula starts with a IF function which will test if  ROW(A1)*5-5>ROWS($A$4:$A$50)

Basically this test will avoid a #REF error in case you copy the formula down to more rows than your range divided by 5. If you do, the formula will just retrieve a blank cell.

In case ROW(A1)*5-5>ROWS($A$4:$A$50) is False, then the following condition will be applied:

INDEX(A$4:A$50,ROW(A1)*5-5)

The index function will grab your range A1:A50 and retrieve the values from the following lines:

ROW(A1)*5-5  → 1*5-5 = line 1

ROW(A2)*5-5  → 2*5-5 = line 5

ROW(A3)*5-5  → 3*5-5 = line 10

...

ROW(A11)*5-5  → 11*5-5 = line 50

Do you like this free website? Please share this page on Google+ or Facebook.