How to calculate Growth from Previous Non Consecutive Dates in Power BI

How to use DAX to calculate Growth from Previous Non Consecutive Dates in Power BI

Scenario:
Suppose we have a Sample Data with Sales (Qnty_Sold) for non consecutive Order Dates as follows:

The simple Data Model is as follows, where Calendar date is connected to the Order date of the sample data.

Now we will calculate the Sales Growth from Previous Non Consecutive dates using the below DAX Logics:

Total Sales = SUM(tbl_Sample[Qnty_Sold])

Prev Sales =

VAR _PrevDate =

    MAXX(

        FILTER( ALLSELECTED(tbl_Calendar[cDate]),

            tbl_Calendar[cDate] < SELECTEDVALUE(tbl_Calendar[cDate])

        ),

    tbl_Calendar[cDate]

    )

VAR _PrevSales =

    CALCULATE( [Total Sales],

        FILTER( ALLSELECTED(tbl_Calendar[cDate]), tbl_Calendar[cDate]= _PrevDate)

        )

RETURN

_PrevSales

Sales Growth =     IF ( [Prev Sales]<>0 && [Total Sales]<>0, DIVIDE([Total Sales],[Prev Sales])-1 )

Result:

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------

How to calculate Running Totals using Power Query in Power BI

How to calculate Cumulative Totals using M-Query in Power BI
Scenario:
Suppose we have a Sample Dataset as follows:

Based on above Data, we need to calculate the Running Totals for Qnty_Sold by Prod_Segment

We can achieve this Scenario using the following M-Query:

let

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZJPawIxEMW/iuxZMH8mk+RYS60teClCD+Ih3Q0aus1KVks/frPLZkGD6+klkN+8zLzZ7Qq6YAtGGCnmhWAMIOrW1vbXta7x8fJS2/IcGu/Ktrv5Q218FU8oi/28w1XCOQeuoq5NqFz7PcEKOrCjNddKYNSP5nK2IUNXwfjSxgPgQOpEMkb0lOuIIgwoHVGKMIW++cqZqJoPJE8kgqQT300gkAGEBGpJuIz6fDSu41aX4N35EuzVfBTLOAqUR13a6oYa+6MJwjFPQqAz25qv2t7DeMpRJExx7LPIvVJfPFnJxAAQKe5YJUpmqTPEPrvc6d2cTLd9iNkk4pbhHadEgchGEY1U37arDt3TdfNjZ0+nU+26OVyvJ81WWwymn6Y9On+YbUwZ9WEdvK2jQWsWdeP+bJi9Buerfn3yKmM+Qt32IonqY31cJM1DxYb2/w==", BinaryEncoding.Base64), Compression.Deflate)), 

let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Order_Date = _t, Order_Id = _t, Prod_Name = _t, Prod_Segment = _t, Country = _t, Qnty_Sold = _t]

    ),

ChangeType =  Table.TransformColumnTypes(Source,{{"Order_Date", type date}, {"Order_Id", Int64.Type}, {"Prod_Name", type text}, {"Prod_Segment", type text}, {"Country", type text}, {"Qnty_Sold", Int64.Type}}),

    

Add_Index = Table.AddIndexColumn(ChangeType, "Index_No", 1, 1, Int64.Type),

/* Running Total Sales*/

Run_Sales = Table.AddColumn(Add_Index, "RT_Sales", 

        each List.Sum(

                List.FirstN(  

                    Add_Index[Qnty_Sold],

                    [Index_No]

                    )

                )   

            ),

/* Running Total Sales by Product Segment */

Seg_Run_Sales = Table.AddColumn(Run_Sales, "Segment_RT_Sales", 

        (OutTable) => 

        List.Sum(

            Table.SelectRows(Add_Index, 

                (InTable) => InTable[Index_No] <= OutTable[Index_No]

                and 

                InTable[Prod_Segment] = OutTable[Prod_Segment]

                )

            [Qnty_Sold]

            )

        )

in

    Seg_Run_Sales

Result:

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------

How to create Custom Date Range Slicer using DAX in Power BI

How to create Custom Date Range table using DAX in Power BI
Scenario:

Suppose we have a Data Model as follows:

The Relationships are as follows:

Based on the above Data Model, we can create a Custom Date Range table using the below DAX expression:

DateRange Period =

VAR _Today = TODAY()

VAR _MinDT = MIN(DimCalendar[DimDate])

/*CALCULATE( MIN(DimCalendar[DimDate]), ALLNOBLANKROW(DimCalendar))*/

VAR _MaxDT = MAX(DimCalendar[DimDate])

/*CALCULATE( MAX(DimCalendar[DimDate]), ALLNOBLANKROW(DimCalendar))*/

VAR _WeekDay_of_Today = WEEKDAY(_Today,1)

VAR _WeekStart_Date = _Today-WEEKDAY(_Today,1)+1

VAR _WeekEnd_Date = _Today-WEEKDAY(_Today,1)+7

VAR _MonthStartDt =

    CALCULATE( STARTOFMONTH(DimCalendar[DimDate]),

        FILTER(ALLNOBLANKROW(DimCalendar),DimCalendar[DimDate] = TODAY()))

VAR _MonthEndDt =

    CALCULATE( ENDOFMONTH(DimCalendar[DimDate]),

       FILTER(ALLNOBLANKROW(DimCalendar),DimCalendar[DimDate] = TODAY()))

VAR _PrevMonthStartDt =

    CALCULATE( EOMONTH(STARTOFMONTH(DimCalendar[DimDate]),-2)+1,

       FILTER(ALLNOBLANKROW(DimCalendar),DimCalendar[DimDate] = TODAY()))

VAR _PrevMonthEndDt =

    CALCULATE( EOMONTH(ENDOFMONTH(DimCalendar[DimDate]),-1),

       FILTER(ALLNOBLANKROW(DimCalendar),DimCalendar[DimDate] = TODAY()))

VAR _CurQtrStartDt =

    CALCULATE( STARTOFQUARTER(DimCalendar[DimDate]),

       FILTER(ALLNOBLANKROW(DimCalendar),DimCalendar[DimDate] = TODAY()))

VAR _CurQtrEndDt =

    CALCULATE( ENDOFQUARTER(DimCalendar[DimDate]),

       FILTER(ALLNOBLANKROW(DimCalendar),DimCalendar[DimDate] = TODAY()))

VAR _PrevQtrStartDt =

    CALCULATE( STARTOFQUARTER(PREVIOUSQUARTER(DimCalendar[DimDate])),

       FILTER(ALLNOBLANKROW(DimCalendar),DimCalendar[DimDate] = TODAY()))

VAR _PrevQtrEndDt =

    CALCULATE( ENDOFQUARTER(PREVIOUSQUARTER(DimCalendar[DimDate])),

       FILTER(ALLNOBLANKROW(DimCalendar),DimCalendar[DimDate] = TODAY()))

VAR _CurYearStartDt =

    CALCULATE( STARTOFYEAR(DimCalendar[DimDate]),

       FILTER(ALLNOBLANKROW(DimCalendar),DimCalendar[DimDate] = TODAY()))

VAR _CurYearEndDt =

    CALCULATE( ENDOFYEAR(DimCalendar[DimDate]),

       FILTER(ALLNOBLANKROW(DimCalendar),DimCalendar[DimDate] = TODAY()))

VAR _PrevYearStartDt =

    CALCULATE( STARTOFYEAR(PREVIOUSYEAR(DimCalendar[DimDate])),

       FILTER(ALLNOBLANKROW(DimCalendar),DimCalendar[DimDate] = TODAY()))

VAR _PrevYearEndDt =

    CALCULATE( ENDOFYEAR(PREVIOUSYEAR(DimCalendar[DimDate])),

       FILTER(ALLNOBLANKROW(DimCalendar),DimCalendar[DimDate] = TODAY()))

VAR _Last7Days_Dates =     DATESBETWEEN( DimCalendar[DimDate],_Today-7, _Today-1)

VAR _Last15Days_Dates =     DATESBETWEEN( DimCalendar[DimDate],_Today-15, _Today-1)

VAR _ThisWeek_Dates =     DATESBETWEEN( DimCalendar[DimDate], _WeekStart_Date, _WeekEnd_Date)

VAR _PrevWeek_Dates =     DATESBETWEEN( DimCalendar[DimDate], _WeekStart_Date-7, _WeekEnd_Date-7)

VAR _MTD_Dates =       DATESBETWEEN( DimCalendar[DimDate], _MonthStartDt, TODAY())

VAR _CurMonth_Dates =     DATESBETWEEN( DimCalendar[DimDate], _MonthStartDt, _MonthEndDt)

VAR _PrevMonth_Dates =     DATESBETWEEN( DimCalendar[DimDate], _PrevMonthStartDt, _PrevMonthEndDt)

VAR _QTD_Dates =     DATESBETWEEN( DimCalendar[DimDate], _CurQtrStartDt, TODAY())

VAR _CurQtr_Dates =     DATESBETWEEN( DimCalendar[DimDate], _CurQtrStartDt, _CurQtrEndDt)

VAR _PrevQtr_Dates =     DATESBETWEEN( DimCalendar[DimDate], _PrevQtrStartDt, _PrevQtrEndDt)

VAR _YTD_Dates =     DATESBETWEEN( DimCalendar[DimDate], _CurYearStartDt, TODAY())

VAR _CurYear_Dates =     DATESBETWEEN( DimCalendar[DimDate], _CurYearStartDt, _CurYearEndDt)

VAR _PrevYear_Dates =     DATESBETWEEN( DimCalendar[DimDate], _PrevYearStartDt, _PrevYearEndDt)

VAR _All_Dates = DATESBETWEEN( DimCalendar[DimDate], _MinDT, _MaxDT)

VAR _Custom_Dates = DATESBETWEEN( DimCalendar[DimDate], BLANK(), BLANK())

VAR _All = ADDCOLUMNS(_All_Dates,

        "Date_Range", "(All)",

        "Range_Index", 1

        )

VAR _Day_Range =

    SELECTCOLUMNS(

            {

            ( _Today, "Today",2),

            ( _Today-1,"Previous Day",3)

            },

        "DimDate",  [Value1],

        "Date_Range", [Value2],

        "Range_Index", [Value3]

            )

VAR _Last7Days =

    ADDCOLUMNS( _Last7Days_Dates, "Date_Range", "Last 7 Days", "Range_Index", 4 )

VAR _Last15Days =

    ADDCOLUMNS( _Last15Days_Dates, "Date_Range", "Last 15 Days", "Range_Index", 5)

VAR _ThisWeek =

    ADDCOLUMNS( _ThisWeek_Dates, "Date_Range", "This Week", "Range_Index", 6)

VAR _PrevWeek =

    ADDCOLUMNS(_PrevWeek_Dates,"Date_Range", "Previous Week", "Range_Index", 7)

VAR _MTD =

    ADDCOLUMNS(_MTD_Dates,"Date_Range", "MTD","Range_Index", 8)

VAR _CurMonth =

    ADDCOLUMNS(_CurMonth_Dates, "Date_Range", "Cur Month", "Range_Index", 9)

VAR _PrevMonth =

    ADDCOLUMNS(_PrevMonth_Dates,"Date_Range", "Prev Month", "Range_Index", 10)

VAR _QTD =

    ADDCOLUMNS(_QTD_Dates, "Date_Range", "QTD","Range_Index", 11)

VAR _CurQTR =

    ADDCOLUMNS(_CurQtr_Dates, "Date_Range", "Cur Quarter", "Range_Index", 12)

VAR _PrevQtr =

    ADDCOLUMNS(_PrevQtr_Dates, "Period_Type", "Prev Quarter", "Range_Index", 13)

VAR _YTD =

    ADDCOLUMNS(_YTD_Dates, "Date_Range", "YTD","Range_Index", 14)

VAR _CurYear =

    ADDCOLUMNS(_CurYear_Dates, "Date_Range", "Cur Year", "Range_Index", 15)

VAR _PreYear =

    ADDCOLUMNS(_PrevYear_Dates, "Date_Range", "Prev Year", "Range_Index", 16)

VAR _Custom =

    ADDCOLUMNS(_Custom_Dates, "Date_Range", "Custom", "Range_Index", 17)

RETURN

UNION(

    _ALL, _Day_Range, _Last7Days,_Last15Days,

    _ThisWeek,_PrevWeek,

    _MTD, _CurMonth, _PrevMonth,

    _QTD, _CurQTR,_PrevQtr,

    _YTD, _CurYear, _PreYear

    )

The result of the above Date Range table is as follows:

Notes:
This table is derived based on the standard Date table (DimCalendar) from the Data Model.

This is a disconnected table which is not required to connect to the Model.

We can use the Range_Index to sort the Date_Range Column.
We have used the ALLNOBLANKROW(DimCalendar) to avoid the Circular dependency error.

-----------------------------------------------------------------------------

Now we will use this custom Date_Range column as a Slicer to filter the data in Report. Lets check how this Slicer works on a Visual.

First lets create a Sales measure needed for the Visual:

Units Sold = SUM(tbl_Sales[Units_Sold])

Next create a Measure which checks whether the Date(from DimCalendar) is within the range of selected custom Date Range. It returns 1 for true and 0 for false.

Is In DateRange =

VAR _MaxDt = MAX(DimCalendar[DimDate])

VAR _IsInDateRange = INT(_MaxDt IN VALUES('DateRange Period'[DimDate]))

RETURN

_IsInDateRange

We will use this Measure as Visual Level filter to filter Dates (from DimCalendar) on X-Axis.

Result:

Visual Level Filter: [Is In DateRange] is 1.

The Date used on X-Axis is from main date table DimCalendar[DimDate] of the Data Model.

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------