Scenario:
Suppose we have the Data Model 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:
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:
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.