How to calculate Average and Cumulative Average Sales for a Selected Date Range in Power BI

How to calculate Average nd Cumulative Average Sales for a Month based on a selected Date Range in Power BI

Scenario:
Suppose we have the Data Model as follows.

The Relationships are as follows:

From above Model, we need to define the below Measures to calculate the Cumulative Sales Average Sales, Cumulative Average Sales, as discussed below:

Net Sales = SUMX ( tbl_Sales, ( tbl_Sales[Unit_Price] * tbl_Sales[Units_Sold] ))

The Sales for Days Selected is nothing but the [Net Sales]

Sales for Days Selected = [Net Sales]

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

The below Measure returns the Cumulative Total Sales for the Period of Dates Selected:

Cum Sales for Selected Days =

VAR vLastDTofSales = MAX(tbl_OrderDetails[Order_Date])

VAR MinDt = MIN(tbl_Calendar[cDate])

VAR vMaxDt = MAX(tbl_Calendar[cDate])

VAR vMinDtAdj = CALCULATE( MIN(tbl_Calendar[cDate]), ALLSELECTED(tbl_Calendar))

VAR vCumSales =

    CALCULATE( [Net Sales],

        FILTER( ALLSELECTED(tbl_Calendar),

            tbl_Calendar[cDate]>= vMinDtAdj &&

            tbl_Calendar[cDate]<= vMaxDT

            )

        )

RETURN

    IF(MinDt<=vLastDTofSales, vCumSales)

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

The below Measure returns the Cumulative Days for the Period of Dates Selected:

Cumulative Days Selected =

VAR vLastDTofSales = MAX(tbl_OrderDetails[Order_Date])

VAR vFirstSelectDT = MIN(tbl_Calendar[cDate])

VAR vMinDT = CALCULATE( MIN(tbl_Calendar[cDate]), ALLSELECTED(tbl_Calendar))

VAR vMaxDT = MAX(tbl_Calendar[cDate])

VAR vDays = CALCULATE(COUNT(tbl_Calendar[cDate]),

                FILTER(ALLSELECTED(tbl_Calendar),

                    tbl_Calendar[cDate]>=vMinDT && tbl_Calendar[cDate]<=vMaxDT

                )

            )

RETURN

    IF(vFirstSelectDT <= vLastDTofSales, vDays)

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

The below Measure returns the Cumulative Average Sales for the Period of Dates Selected:

Cum Avg Sales for Selected Days =

        DIVIDE( [Cum Sales for Selected Days], [Cumulative Days Selected])

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

The below Measure returns the Cumulative Sales for the Days Passed in Selected Month of the Period of Dates Selected. Lets say we have completed only 24 Days in the Month April, then it returns the Cumulative Sales for only 24 Days instead of 30 Days.

If we select multiple months, then it returns the Cumulative Totals by Month. It works like the MTD Totals (DATESMTD).

Monthly Cum Sales for Days Passed =

VAR vToday = DATE(YEAR(TODAY()), MONTH(TODAY()), DAY(TODAY()))

VAR vMinDt = MIN(tbl_Calendar[cDate])

VAR vMinDtSelect =

    CALCULATE(MIN(tbl_Calendar[cDate]), ALLSELECTED(tbl_Calendar) )

VAR vMaxDt = MAX(tbl_Calendar[cDate])

VAR vMaxDtAdj = IF(vMaxDT<=vToday-1, vMaxDT, vToday-1)

VAR vMonthlyCumSales =

    CALCULATE( [Net Sales],

        DATESMTD(tbl_Calendar[cDate]),

           /* DATESBETWEEN(tbl_Calendar[cDate], vMinDtSelect, vMaxDtAdj) */         

            FILTER(ALLSELECTED(tbl_Calendar[cDate]),

                tbl_Calendar[cDate]>=vMinDtSelect && tbl_Calendar[cDate]<=vMaxDtAdj

                )

           

            )  

RETURN

    vMonthlyCumSales

    //IF(MinDt<=vMaxDtAdj, vMonthlyCumSales)

The below Measure returns the Cumulative Sales for the Days Passed in all Selected Months of the Period of Dates Selected. Lets say we have completed only 24 Days in the Month April, then it returns the Cumulative Sales for only 24 Days instead of 30 Days.

If you select multiple Months, It returns continuous Cumulative Totals for the Selected Months.

Cum Sales for Days Passed =

VAR vToday = DATE(YEAR(TODAY()), MONTH(TODAY()), DAY(TODAY()))

VAR vLastDTofSales = MAX(tbl_OrderDetails[Order_Date])

VAR MinDt = MIN(tbl_Calendar[cDate])

VAR vMinDtAdj = CALCULATE( MIN(tbl_Calendar[cDate]), ALLSELECTED(tbl_Calendar))

VAR vMaxDt = MAX(tbl_Calendar[cDate])

VAR vMaxDtAdj = IF(vMaxDT<=vToday-1, vMaxDT, vToday-1)

VAR vCumSales =

    CALCULATE( [Net Sales],

        FILTER( ALL(tbl_Calendar),

            tbl_Calendar[cDate]>= vMinDtAdj &&

            tbl_Calendar[cDate]<= vMaxDtAdj

            )

        )

RETURN

    vCumSales

    //IF(MinDt<=vMaxDtAdj, vCumSales)

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

The below Measure returns the Cumulative No. of Days for the Days Passed in the Selected Month of the Period of Dates Selected. Lets say we have completed only 24 Days in a Month April, then it returns the Cumulative No. of Days for only 24 Days instead of 30 Days.

Cumulative Days Passed =

VAR vLastDTofSales = MAX(tbl_OrderDetails[Order_Date])

VAR vFirstSelectDT = MIN(tbl_Calendar[cDate])

VAR vMinDT = CALCULATE( MIN(tbl_Calendar[cDate]), ALLSELECTED(tbl_Calendar))

VAR vToday = DATE(YEAR(TODAY()), MONTH(TODAY()), DAY(TODAY()))

VAR vMaxDT = MAX(tbl_Calendar[cDate])

VAR vMaxDtAdj = IF(vMaxDT<=vToday-1, vMaxDT, vToday-1)

VAR vDaysPassed =

    COUNTROWS(FILTER(ALLSELECTED(tbl_Calendar),

         tbl_Calendar[cDate]>=vMinDT && tbl_Calendar[cDate]<=vMaxDtAdj))

RETURN

    vDaysPassed

    //IF(vFirstSelectDT <= vMaxDtAdj, vDaysPassed)

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

The below Measure returns the Cumulative Average Sales for the Days Passed in a Selected Month of the Period of Dates Selected. Lets say we have completed only 24 Days in a Month April, then it returns the Cumulative Average Sales for only 24 Days instead of 30 Days.

Cum Avg Sales for Days Passed =

        DIVIDE( [Cum Sales for Days Passed], [Cumulative Days Passed])

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

The below Measure returns the Total No. of Days in the Period of Dates Selected. If you select only on Month, say April, it returns 30; if you select March and April it returns the 31, 30.

Total Days In Month = DAY(EOMONTH(MIN(tbl_Calendar[cDate]),0))

The below Measure returns the Total No. of Days in the Period of Dates Selected. If you select only on Month, say April, it returns 30; if you select March and April it returns the 61(31+30).

Total Days Selected = COUNTROWS(ALLSELECTED(tbl_Calendar))

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

The below Measure returns the Total No. of Days in the Period of Dates Selected. If you select only on Month, say April, it returns 30; if you select March and April, where only 24 Days  are completed in April, then it returns the result as 31(March), 24(April).

Total Days Passed in Month =

VAR vToday = DATE(YEAR(TODAY()), MONTH(TODAY()), DAY(TODAY()))

VAR vMinDt = MIN(tbl_Calendar[cDate])

VAR vMaxDt = CALCULATE( MAX(tbl_Calendar[cDate]), ALLSELECTED(tbl_Calendar))

VAR vMaxDtAdj = IF(vMaxDT<=vToday-1, vMaxDT, vToday-1)

VAR vEndDT = EOMONTH(vMinDt,0)

VAR vAdjDaysInMonth = DAY(vMaxDtAdj)

VAR DaysInMonth = DAY(EOMONTH(vMinDt,0))

RETURN

IF(vEndDT > vMaxDtAdj, vAdjDaysInMonth,DaysInMonth )

The below Measure returns the Total No. of Days in the Period of Dates Selected. If you select only on Month, say March, it returns 31; if you select March and April, where only 24 Days  are completed in April, then it returns the result as  55 = 31(March)+24(April).

Total Days Passed =

VAR vFirstSelectDT = MIN(tbl_Calendar[cDate])

VAR vToday = DATE(YEAR(TODAY()), MONTH(TODAY()), DAY(TODAY()))

VAR vMinDt = CALCULATE( MIN(tbl_Calendar[cDate]), ALLSELECTED(tbl_Calendar))

VAR vMaxDt = CALCULATE( MAX(tbl_Calendar[cDate]), ALLSELECTED(tbl_Calendar))

VAR vMaxDtAdj = IF(vMaxDT<=vToday-1, vMaxDT, vToday-1)

VAR vDaysAdj = DATEDIFF(vMinDt,vMaxDtAdj, DAY)+1

    /* DATESBETWEEN(tbl_Calendar[cDate], vMinDt, vMaxDtAdj))

        tbl_Calendar[cDate]>=vMinDt && tbl_Calendar[cDate]<=vMaxDtAdj)*/

RETURN

 IF(vFirstSelectDT <= vMaxDtAdj, vDaysAdj)

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

The result of the above formulas are as follows :

Related articles:
https://excelkingdom.blogspot.com/2023/04/how-to-calculate-total-days-and-days.html
https://excelkingdom.blogspot.com/2023/04/how-calculate-monthly-average-sales.html

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

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

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