How to Calculate Number of Days Passed in a Week of the Month in Power BI

How to Calculate Cumulative Number of Days Passed in a Week of the Month in Power BI

Scenario:
Suppose we have the Data Model as follows.

The Relationships are as follows:

Based on the above Data Model, we will calculate the below Measures, with an assumption that the last Sales Date is 03-May-2023, and we are in the Middle of Week 18:

The below measure return the Total No. of Days in the Selected Week of the Month. It return the values like 1, 3, 5, 7, based on the No. of days available in that Selected Week.

Generally we will get maximum value of 7 Days, however, in the Month Start and Month End weeks, we will get the lower number.

Total Days in Week =

    VAR vMaxSaleWeek =

        CALCULATE(WEEKNUM(MAX(tbl_OrderDetails[Order_Date])),

                ALLSELECTED(tbl_Calendar))

    VAR vMaxWeek = WEEKNUM(MAX(tbl_Calendar[cDate]))

    VAR vWeekMinDt = CALCULATE(MIN(tbl_Calendar[cDate]),

                ALLSELECTED(tbl_Calendar),

                tbl_Calendar[Week_No] = MIN(tbl_Calendar[Week_No]))

    VAR vWeekMaxDt = CALCULATE(MAX(tbl_Calendar[cDate]),

                ALLSELECTED(tbl_Calendar),

                tbl_Calendar[Week_No] = MIN(tbl_Calendar[Week_No]))

     VAR vTotalDaysInWeek = DATEDIFF(vWeekMinDt, vWeekMaxDt, DAY)+1

RETURN

    vTotalDaysInWeek

    //IF(vMaxWeek<=vMaxSaleWeek,vTotalDaysInWeek)

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

The below Measure returns the Cumulative No. of Days in a Week, like 1,2,3,4,5,6,7.

Cum Days in Week =

    VAR vMaxSaleDT =

        CALCULATE(MAX(tbl_OrderDetails[Order_Date]),

                    ALLSELECTED(tbl_Calendar))

    VAR vMaxSaleWeek =

        CALCULATE(WEEKNUM(MAX(tbl_OrderDetails[Order_Date])),

                    ALLSELECTED(tbl_Calendar))

    VAR vMaxDt = MAX(tbl_Calendar[cDate])

    VAR vMaxWeek = WEEKNUM(MAX(tbl_Calendar[cDate]))

    VAR vMinWeekDT =

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

                    tbl_Calendar[Week_No] = MAX(tbl_Calendar[Week_No]))

    VAR vCumDaysInWeek =

        CALCULATE(COUNT(tbl_Calendar[Week_No]),

                    tbl_Calendar[cDate]>=vMinWeekDT &&  tbl_Calendar[cDate]<=vMaxDt )

RETURN

    vCumDaysInWeek

    //IF(vMaxWeek<=vMaxSaleWeek,vCumDaysInWeek)

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

The below returns the Total Days that Completed in a Week. It return the values like 1, 3, 7 based on the Closed No. of Days in Week.

Total Days Pass in Week =

    VAR vMaxSaleDt =

        CALCULATE(MAX(tbl_OrderDetails[Order_Date]), ALLSELECTED(tbl_Calendar))

    VAR vMaxSaleWeek =

        CALCULATE(WEEKNUM(MAX(tbl_OrderDetails[Order_Date])),

                    ALLSELECTED(tbl_Calendar))

    VAR vMaxWeek = WEEKNUM(MAX(tbl_Calendar[cDate]))

    VAR vWeekMinDt =

        CALCULATE(MIN(tbl_Calendar[cDate]),

                    ALLSELECTED(tbl_Calendar),

                    tbl_Calendar[Week_No] = MIN(tbl_Calendar[Week_No]))

    VAR vWeekMaxDt =

        CALCULATE(MAX(tbl_Calendar[cDate]),

                    ALLSELECTED(tbl_Calendar),

                    tbl_Calendar[Week_No] = MAX(tbl_Calendar[Week_No]))

    VAR vAdjMaxDt = IF (vWeekMaxDt<=vMaxSaleDT, vWeekMaxDt, vMaxSaleDT)

    VAR vTotalDaysPassInWeek = DATEDIFF(vWeekMinDt,vAdjMaxDt,DAY)+1

RETURN

    IF(vMaxWeek<=vMaxSaleWeek,vTotalDaysPassInWeek,0)

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

The below Measure returns the Cumulative No. of Days that Passed in a Week. It return the values like 1,2,3,4.....7

Cum Days Pass in Week =

    VAR vMaxSaleDT =

        CALCULATE(MAX(tbl_OrderDetails[Order_Date]), ALLSELECTED(tbl_Calendar))

    VAR vMaxSaleWeek =

        CALCULATE(WEEKNUM(MAX(tbl_OrderDetails[Order_Date])),

                    ALLSELECTED(tbl_Calendar))

    VAR vMaxDt = MAX(tbl_Calendar[cDate])

    VAR vMaxWeek = WEEKNUM(MAX(tbl_Calendar[cDate]))

    VAR vMinWeekDT =

        CALCULATE(MIN(tbl_Calendar[cDate]),

                    ALLSELECTED(tbl_Calendar),

                    tbl_Calendar[Week_No] = MAX(tbl_Calendar[Week_No]))

    VAR vWeekMaxDt =

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

                    tbl_Calendar[Week_No] = MAX(tbl_Calendar[Week_No]))

    VAR vAdjMaxDt = IF (vMaxDt<=vMaxSaleDT, vMaxDt, vMaxSaleDT)

    VAR vCumDaysPassInWeek = CALCULATE(COUNT(tbl_Calendar[Week_No]),

                    tbl_Calendar[cDate]>=vMinWeekDT &&

                    tbl_Calendar[cDate]<=vAdjMaxDt )

RETURN

    IF(vMaxWeek<=vMaxSaleWeek,vCumDaysPassInWeek,0)

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

The below Measure returns the Cumulative Total no. of Days from each Week. It returns the values like 1, 8, 15, 22...

Cum Total Days in Week =

    VAR vMaxSaleWeek =

        CALCULATE(WEEKNUM(MAX(tbl_OrderDetails[Order_Date])),

                    ALLSELECTED(tbl_Calendar))

    VAR vMaxWeek = WEEKNUM(MAX(tbl_Calendar[cDate]))

    VAR vMaxDt = MAX(tbl_Calendar[cDate])

    VAR vWeekMinDt = CALCULATE(MIN(tbl_Calendar[cDate]),

                    ALLSELECTED(tbl_Calendar),

                    tbl_Calendar[Week_No] <= MAX(tbl_Calendar[Week_No]))

    VAR vWeekMaxDt = CALCULATE(MAX(tbl_Calendar[cDate]),

                    ALLSELECTED(tbl_Calendar),

                    tbl_Calendar[Week_No] <= MAX(tbl_Calendar[Week_No]))

    VAR vCumTotalDaysInWeek = DATEDIFF(vWeekMinDt,vWeekMaxDt,DAY)+1

RETURN

    vCumTotalDaysInWeek

    //IF(vMaxWeek<=vMaxSaleWeek,vCumTotalDaysInWeek)

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

The below Measure returns Cumulative Total no. of Days Passed from each Week. It returns the values like 1, 8, 15, 22...

Cum Total Days Pass in Week =

    VAR vMaxSaleDT =

        CALCULATE(MAX(tbl_OrderDetails[Order_Date]), ALLSELECTED(tbl_Calendar))

    VAR vMaxSaleWeek =

        CALCULATE(WEEKNUM(MAX(tbl_OrderDetails[Order_Date])),

                        ALLSELECTED(tbl_Calendar))

    VAR vMaxWeek = WEEKNUM(MAX(tbl_Calendar[cDate]))

    VAR vMaxDt = MAX(tbl_Calendar[cDate])

    VAR vWeekMinDt = CALCULATE(MIN(tbl_Calendar[cDate]),

                        ALLSELECTED(tbl_Calendar),

            tbl_Calendar[Week_No] <= MAX(tbl_Calendar[Week_No]))

    VAR vWeekMaxDt = CALCULATE(MAX(tbl_Calendar[cDate]),

                        ALLSELECTED(tbl_Calendar),

            tbl_Calendar[Week_No] <= MAX(tbl_Calendar[Week_No]))

    VAR vAdjMaxDt = IF (vWeekMaxDt<=vMaxSaleDT, vWeekMaxDt, vMaxSaleDT)

    VAR vCumTotalDaysPassInWeek = DATEDIFF(vWeekMinDt,vAdjMaxDt,DAY)+1

RETURN

    IF(vMaxWeek<=vMaxSaleWeek,vCumTotalDaysPassInWeek,0)

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

Now we can see the result of all the measures as shown below:

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

Similarly we can find the No. of Days in Year, Quarter, Month, Week Start Date and Week End Date as follows:

Total Days In Year =

        DATEDIFF(STARTOFYEAR(tbl_Calendar[cDate]),

                        ENDOFYEAR(tbl_Calendar[cDate]), DAY)+1

Total Days In Quarter =

        DATEDIFF(STARTOFQUARTER(tbl_Calendar[cDate]),

                        ENDOFQUARTER(tbl_Calendar[cDate]), DAY)+1

Total Days In Month =

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

    VAR vMaxSaleDT =

        CALCULATE(MAX(tbl_OrderDetails[Order_Date]), ALLSELECTED(tbl_Calendar))

    VAR vMonthStartDt = STARTOFMONTH(tbl_Calendar[cDate])

    VAR vMonthEndDt = ENDOFMONTH(tbl_Calendar[cDate])

    VAR vDaysDiff = DATEDIFF(vMonthStartDt,vMonthEndDt,DAY)+1

RETURN

    IF(vMonthStartDt<=vMaxSaleDT,vDaysDiff)

Total Days Pass in Month =

    VAR vMaxSaleDT =

        CALCULATE(MAX(tbl_OrderDetails[Order_Date]), ALLSELECTED(tbl_Calendar))

    VAR vMonthStartDt = STARTOFMONTH(tbl_Calendar[cDate])

    VAR vMonthEndDt = ENDOFMONTH(tbl_Calendar[cDate])

    VAR vAdjMaxDt = IF (vMonthEndDt<=vMaxSaleDT, vMonthEndDt, vMaxSaleDT)

    VAR vDaysDiff = DATEDIFF(vMonthStartDt,vAdjMaxDt,DAY)+1

RETURN

    IF(vMonthStartDt<=vMaxSaleDT,vDaysDiff)

Week Start Date =

        MIN(tbl_Calendar[cDate])-WEEKDAY(MIN(tbl_Calendar[cDate]))+1

Week End Date =

        MIN(tbl_Calendar[cDate])-WEEKDAY(MIN(tbl_Calendar[cDate]))+7

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

Result:

Related Articles:
https://excelkingdom.blogspot.com/2023/04/how-to-calculate-total-days-and-days.html

https://excelkingdom.blogspot.com/2023/04/how-to-calculate-average-and-cumulative.html

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

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

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