cancel
Showing results for
Did you mean:
Frequent Visitor

Month To Date - Default last Date as Yesterday - Help

Dear Power Bi Gurus,

I have set up my model to count Month To Date values for this Year and Last Year as shown at the picture.

This is possible by manually selecting on a "Date Hierarchy" slicer. My results are correct this way.

However, as this will be a daily automated report, I would like to always have Yesterday Date as my most recent date on my Month to Date calculations.

This way my report will always reflect the "Start of Month - Yesterday" time period.

1 ACCEPTED SOLUTION
Resolver III

Dear @airportGR ,

I enriched the sample data as follows.

The following formula should do it for the last year.

if you want to have it for two years ago please change the "YEAR(TODAY())-1" to "YEAR(TODAY())-2".

Appreciate your Kudos.

Best regards

Mikelytics

4 REPLIES 4
Frequent Visitor

Dear @Mikelytics

Solution 1 works perfectly fine without any selection needed on the slicer

Solution 2 in order to work the slicer has to be selected (specific date ie 16/1/2021)

I am moving forward with Solution 1 (many thanks!)

Could you please advise on how to have the same MTD results for -1 and -2 years back (following Solution 1)

Thank you very much

Resolver III

Dear @airportGR ,

I enriched the sample data as follows.

The following formula should do it for the last year.

if you want to have it for two years ago please change the "YEAR(TODAY())-1" to "YEAR(TODAY())-2".

Appreciate your Kudos.

Best regards

Mikelytics

Frequent Visitor

Dear @Mikelytics following your Solution 1 and adding the extra code for Last Year (-1) and (-2) solved my problem.

Thank you very much, I hope that this thread will help others too.

Resolver III

Hi  @airportGR ,

did you try to solve this by putting the filter in the measure? I have two possible solutions based on the following sample data.

(in Power BI I deleted the first row from the sample data)

Solution 1: if you want to use it in a KPI visual

Solution 2: if you want to use it in a table or similar:

Hope this helps.

Best regards

Mikelytics

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

Announcements