Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ggargan
Frequent Visitor

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

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

ggargan_0-1667296464082.png

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

ggargan_1-1667296509322.png

 

 

7 REPLIES 7
v-jianboli-msft
Community Support
Community Support

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
Helper I
Helper I

just uncheck "include today"

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

yogeshmaney_0-1667303179960.png

 

keshavagrawal27
Resolver I
Resolver I
Jihwan_Kim
Super User
Super User

Hi,

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

 

https://dax.guide/datesinperiod/

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here and Go to My LinkedIn Page


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

 

Original Chart

ggargan_0-1667298945819.png

Using the calculate and Dates in period functions

ggargan_2-1667299800479.png

 

 

 

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.