Scenario :
If the Current Date falls on the Day “01”, and Week Day falls either on {“Sat”, “Sun”, “Mon”}, then the report Refresh Date should be consider as the Last Fridays date.
This is one of the common Business scenario, where the user wants to see the refresh date of the Report as closed(last) Month’s , last Week-Friday date, when the user opens the report on the first day “01” of the Month.
We can achieve this in Power BI using DAX as follows :
Suppose the user opens report on “01-Sep-2019”
curDate = DATE(2019,09,01)
Since in the above Date, Day is “01” and it falls on “Sun”, then as per the above scenario, the user will see the refresh Date as “30-Aug-2019” which is the last “Friday” date of the Last Week of the Previous Month, which is the actual reported Month.
refDate =
VAR WkName=FORMAT(WEEKDAY([curDate]),”DDD”)
Return
IF(DAY([curDate])=1&& WkName In {“Sat”,”Sun”,”Mon”},
[curDate]-SWITCH(TRUE(),WkName=”Sat”,1,WkName=”Sun”,2,WkName=”Mon”,3),
[curDate])
Based on the above Scenario based refresh date (refDate) we can calculate the CurMonth and the NxtMonth values as per our need.
CurMonthFY = FORMAT([refDate],”MMM”)&”‘”&RIGHT(YEAR([refDate]),2)
NxtMonthFY = FORMAT(([refDate]+INT(EOMONTH([refDate],0)-[refDate])+7),”MMM”)&”‘”&RIGHT((YEAR([refDate]+INT(EOMONTH([refDate],0)-[refDate])+7)),2)
Result:
Be the first to comment