How to calculate Total Days and Days Passed in a Month based on Selected Date Range in Power BI

How to calculate Total Days and Days Passed in 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 calculate the measures for the below requirements:

1. Total no. of Days in the Selected Date Range ( Eg: 30, 60, 120..)

2. The no. of Days in a Selected Month ( Eg: 31, 28, 30 )

3. The no. of Days passed in a Month : 

If the Date <= (Today-1) then we will consider only the Days up to (Today-1) for the Current Month. The no. of Days and Passed Days will be equal for a closed Month.

4. Remaining Days in Month :  

The difference of [Total Days in a Selected Month] and the [Total Days passed in a Month]

5. Cumulative Total no. of Days

6. Cumulative Total no. of Days Passed in the Month

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

To fulfill the above requirement, we need to create the below Measures:

The below measure returns the Total no. of days(Eg: 28, 30, 31) in each Selected Month.

Total Days In Month =

        DAY(EOMONTH(MIN(tbl_Calendar[cDate]),0))

The below measure returns Cumulative Total no. of days (1,2,..30,31) in each Selected Month.

Cum Days in Month =

    /*CALCULATE(COUNTROWS(ALLSELECTED(tbl_Calendar)),

        DATESMTD(tbl_Calendar[cDate])) */

    CALCULATE(COUNT(tbl_Calendar[cDate]),

        DATESMTD(tbl_Calendar[cDate]),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 March, it returns 31; if you select March and April, where only 28 Days are completed in April, then it returns the result 31(March), 28(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 vDaysInMonth = DAY(EOMONTH(vMinDt,0))

RETURN

IF(vEndDT > vMaxDtAdj, vAdjDaysInMonth,vDaysInMonth )

The below Measure returns the Cumulative Total No. of Days in each Selected Month. If you select only on Month, say March, it returns 1, 2,3,... 31; if you select March and April, where only 28 Days  are completed in April, then it returns the result as 1,2,3..31(March), and 1,2,3,.... 28(April).

Cum Days Pass in Month =

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 vCumDaysPassInMonth =

    CALCULATE(COUNT(tbl_Calendar[cDate]),

        DATESMTD(tbl_Calendar[cDate]),

        DATESBETWEEN(tbl_Calendar[cDate], vMinDtSelect, vMaxDtAdj)

        /*

        FILTER(ALLSELECTED(tbl_Calendar[cDate]),

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

            )

        */

        )  

RETURN

    vCumDaysPassInMonth

  //IF(vMinDt <= vMaxDtAdj, vCumDaysPassInMonth)

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

The difference of [Total Days in a Selected Month] and the [Total Days passed in a Month]

Remaining Days in Month =

[Total Days In Month]-[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,DaysInMonth-vAdjDaysInMonth,0)

*/

The below measure returns the Total no. of Days from all Selected Months:

Total Days Selected = COUNTROWS(ALLSELECTED(tbl_Calendar))

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

The below measure returns the Cumulative Total no. of Days from all Selected Months:

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 vCumDays = CALCULATE(COUNT(tbl_Calendar[cDate]),

                FILTER(ALLSELECTED(tbl_Calendar),

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

                )

            )

RETURN

    IF(vFirstSelectDT <= vLastDTofSales, vCumDays)

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

The below measure returns the Total no. of Passed Days from all the Selected Months. If you select only on Month, say March, it returns 31; if you select March and April, where only 28 Days are completed in April, then it returns the result as 31+28 = 59.

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 vDaysPass = /* DATEDIFF(vMinDt,vMaxDtAdj, DAY)+1 */

        CALCULATE(COUNTROWS(ALLSELECTED(tbl_Calendar)),

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

RETURN

    vDaysPass

/*  IF(vFirstSelectDT <= vMaxDtAdj, vDaysPass) */

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

The below Measure returns the Cumulative Total No. of Days in the Period of Dates Selected. If you select only on Month, say March, it returns 1, 2,3,... 31; if you select March and April, where only 28 Days  are completed in April, then it returns the result as 1,2,3..31(March), and 32,33,34,.... 59(April).

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 vDaysPass =

    COUNTROWS(FILTER(ALLSELECTED(tbl_Calendar),

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

RETURN    

    vDaysPass

   /* IF(vFirstSelectDT <= vMaxDtAdj, vDaysPass) */

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

Now we can view the result of the above Measures for various cases as per below.

Case-1 : For the Current Month ( Say April'2023, Partially Completed) :

Lets say, we have completed only 28 Days in the Current Month April.

Result:

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

Case-2 : For the Current Month ( Say April'2023) and Previous Month (Completed):

Lets say, we have completed only 28 Days in Current Month April, and March is Completed.

Result:

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

Case-3 : For the Selected Dates:

Lets say, we have selected some Dates from a Month, Apr'2023.

Result:

Notes : 

In the above Case, the [Total Days in Month] = 10 returned based on the last Selected Date in a Month.

The [Total Days Passed] is behaving differently in this case, it returns "7" days based on the Min Selected Date and Max Selected Date.

For this one, we can try DATEDIFF(vMinDt,vMaxDtAdj, DAY)+1 as well.

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

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

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