How to choose Custom refresh Date and Month based on the Current Date Scenario in Power BI | Power BI Blog

How to choose refresh Date as Last Friday date of Closed Week based on the Current Date and Week Day
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.
IF(DAY([curDate])=1&& WkName In {“Sat”,”Sun”,”Mon”},

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)


Source link

Be the first to comment

Leave a Reply

Your email address will not be published.