cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
airportGR
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. 

 

Could you please help me on how to automatically have yesterday as my last date?

 

Thank you in advance. 

 

Capture.JPG

 

 

1 ACCEPTED SOLUTION

Dear @airportGR ,

 

 I enriched the sample data as follows.

picture_2.PNG

The following formula should do it for the last year.

picture_1.PNG

 

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

 

Did I solve your requests? Please mark my post as solution.

 

Appreciate your Kudos.

 

Best regards

Mikelytics

View solution in original post

4 REPLIES 4
airportGR
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

Dear @airportGR ,

 

 I enriched the sample data as follows.

picture_2.PNG

The following formula should do it for the last year.

picture_1.PNG

 

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

 

Did I solve your requests? Please mark my post as solution.

 

Appreciate your Kudos.

 

Best regards

Mikelytics

View solution in original post

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.

Mikelytics
Resolver III
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.

picture_1.PNG

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

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

picture_2.PNG

 

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

picture_3.PNG

Hope this helps.

 

Best regards

Mikelytics

 

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

 

Did I solve your requests? Please mark my post as solution.

 

Appreciate your Kudos.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.