DAX to display the last 12 months from the MAX Date in the dataset

[ggargan]

Hello,

I have a line chart which is using a measure 

It is currently filtered for the last 12 months using the filter pane

However with this way i noticed it doing this filter based on todays date - not the Maximum date in the dataset which is September 2022.

I want to create a measure that shows the last 12 months of a measure from the Maxiumum date in the dataset - not sure how to do this.

Could anyone help?

This is my visual - See as it drops off to zero for a month where there is no data inputted yet

This is how it is currently filtered - but includes the current month, 

[v-jianboli-msft]

Hi @ggargan ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or if you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[yogeshmaney]

just uncheck "include today"

[ggargan]

Hello, i tried that it does not work. It is also not a option for calendar months

[yogeshmaney]

[keshavagrawal27]

Hello @ggargan,

Please check below article:

https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?MessageKey=58d182a0-...

[Jihwan_Kim]

Hi,

One of ways to solve this is to use DATESINPERIOD dax function with CALCULATE dax function in your measure.

https://dax.guide/datesinperiod/

[ggargan]

Hello, i tried this but the result was not as expected

Original Chart

Using the calculate and Dates in period functions

This was my DAX

TEST Total fires 12 M = calculate(sum(Metrics[Value]), Metrics[Attribute]="Fire Event [unit]", DATESINPERIOD(DateTable1[Date], max(Metrics[MonthYear]), -12, MONTH))

It seems to be cumulative, and it also is showing more than 12 months. Not sure where i am going wrong with this