Time functions in DAX

Time functions in DAX are besides simple calculation functions, the most useful functions; mainly because time slicers are one of the most frequently used slicers in Power BI.

New functions are coming to DAX (Data Analysis Expression Language) regularly, and just recently, the function QUARTER was added.  And this gave me thinking; why not do a standardized DAX, to be added as a “time dimension” to every Power BI, that I create.

Upon opening new Power BI Desktop document, go to Enter Data and add two rows, representing the start and end time:

I have inputed two rows:

• 1978/08/29
• 2019/12/07

After this, go to Modeling tab and select New Table:

Simply copy/paste the following code:

DimTime =
     VAR V_Calendar = CALENDARAUTO()
RETURN
    GENERATE (
        V_Calendar;
        VAR V_Calendar_date = [Date]
        VAR LetoDatum = YEAR(V_Calendar_date)
        VAR Kvartal = CEILING(MONTH(V_Calendar_date)/3;1)
        VAR MesecCifra = MONTH(V_Calendar_date)
        VAR DanN = DAY(V_Calendar_date)
        VAR KonecMeseca = EOMONTH(V_Calendar_date; 0)
        VAR TedenCifra = WEEKNUM(V_Calendar_date; 2)
        VAR DanVTednu = WEEKDAY(V_Calendar_date;2)
        Return ROW(
            "Day"; V_Calendar_date;
            "OnlyDay"; DanN;
            "Year"; LetoDatum;
            "Month (number)"; MesecCifra;
            "Quarter"; Kvartal;
            "Month"; FORMAT(V_Calendar_date; "mmmm");
            "DayOfWeek"; DanVTednu;
            "NameOfWeek"; FORMAT(DanVTednu+1; "dddd");
            "Year Month"; FORMAT (V_Calendar_date; "mmm yy");
            "End Of Month"; FORMAT (KonecMeseca; "dd mmm yy");
            "Week Number"; TedenCifra
        )
    )

And it should look like this:

Yielding the corresponding fields in the data structure:

Notice that the DimTime will be automatically populated within the minimum and maximum years; so the range goes from 1978/01/01 until 2019/12/31. You can also add the relation if you want to filter based on the context, but it will have no effect on populating the DimTime table.

Now I can visualize and count, for example, how many Friday, 13th (since one is just around the corner) are between 1978/01/01 and 2019/31/12. And I can do this simply by adding couple of slicers:

And by selecting Friday and 13, I get the result: 74.

And If I want to check this; let’s see, how many are in Year 2019:

Only two! 🙂

Power BI (with DAX) is available at GitHub here.

Happy data slicing 🙂

 

 

‹ Advent of Code 2019 challenge with R

Custom Power BI filter pane with hide/show feature ›

Tagged with: DAX, function, Power BI, time, Time functions
Posted in Uncategorized