cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PBI-Curious
Resolver I
Resolver I

Dax Needed for Trailing Twelve Months

Hello PBI Community-  Good Day!

 

I currently using a TTM Dax measure ( see below) that is giving me a ( YoY $) for 11 months instead of 12 months. I tried changing the -11 to -12, but it still doesn't give me the correct (YoY $) I'm looking for.

 

 

Note:

I confirmed the Dax measure was calculating a (YoY $) for 11 months by exporting the data and creating a schedule in Excel. Making the adjustment in my Excel formular to calculate a (YoY $) with 12 months of data (by month year) yielded the correct (YoY $)

 

 

How would I tweak this measure or should I use another approach.

 

TTM CY = CALCULATE(SUM('SnR 2018 - 2021'[SnR Amount])
    DATESBETWEEN('Calendar'[Date],
FIRSTDATE(
DATEADD(
LASTDATE('Calendar'[Date])
,-11,MONTH
)
),
LASTDATE('Calendar'[Date])
)
)

 

1 ACCEPTED SOLUTION
Mohammad_Refaei
Solution Specialist
Solution Specialist

You could be facing an issue because of the booking date of your values. Do you work with day or month granularity?

For example, if you are working with day granularity then 24-July-2021 will be rolled back to 24-July-2020... it is not the whole month then and this is what happens with the datedd. Is this what you are looking for and you are getting wrong values?

 

It will help if you share a sample of your data model. 

View solution in original post

4 REPLIES 4
Fowmy
Super User IV
Super User IV

@PBI-Curious 

Can you try this measure? Share some data with the expected results if this doesn't

TTM CY = 
    CALCULATE(
        SUM('SnR 2018 - 2021'[SnR Amount]),
        DATESINPERIOD(
            'Calendar'[Date],
            MAX('Calendar'[Date]),
           -12,
           MONTH
        ),
        ALLSELECTED('Calendar')
    )

work.



Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Fowmy

Much appreciate your help!

 

Thank you

Mohammad_Refaei
Solution Specialist
Solution Specialist

You could be facing an issue because of the booking date of your values. Do you work with day or month granularity?

For example, if you are working with day granularity then 24-July-2021 will be rolled back to 24-July-2020... it is not the whole month then and this is what happens with the datedd. Is this what you are looking for and you are getting wrong values?

 

It will help if you share a sample of your data model. 

View solution in original post

m_refaei

understood, makes sense.

 

Thank you

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.