Saturday, April 29, 2023

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

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog