Need Help with Line Chart Aggregation Issue: Two DAX Measures Result in Enormous Values

[Jack_Reacher]

Hello all,

I have the table displayed below which represents the data I want to show on a line graph, the desired outcome is to have two lines, one for the current rolling 12 months, and another for the prior rolling 12 months. I have created the following DAX measures to display the line charts displayed below. The problem I have here is that the values aggregate in an enormous manner over the graph periods, and I can't figure out why this is happening. 

AAAM_Current_R12M = CALCULATE(SUM(AMD[DDep]), DATESINPERIOD('Calendrier'[Date], ENDOFMONTH('Calendrier'[Date]), -12, MONTH))

AAAM_Prior_R12M = CALCULATE(SUM(AMD[DDep]), DATESINPERIOD('Calendrier'[Date],ENDOFMONTH(dateadd('Appointments Manager'[Period date],-12,month)),-12,MONTH))

PeriodCal = FORMAT(Calendrier[Date], "mmm"&" "&"yyyy")

For info, I have a date table named "Calendrier" which is set as the default date table, and the PeriodDate starts from Jan 2021.

I really appreciate your assistance

Thank you

[v-tangjie-msft]

Hi @Jack_Reacher ,

According to your description, here are my steps you can follow as a solution.

(1)My test data is the same as yours.

(2) We can create two measures.

Rolling ddep  current 12 month =
CALCULATE (
    SUM ( AMD[Ddep] ),
    DATESBETWEEN (
        'Calendrier'[Date],
        EDATE ( MIN ( 'Calendrier'[Date] ), -11 ),
        MAX ( 'Calendrier'[Date] )
    )
)

Rolling ddep for previous 12 months =
CALCULATE (
    SUM ( 'AMD'[Ddep] ),
    DATESBETWEEN (
        'Calendrier'[Date],
        EDATE ( MIN ( 'Calendrier'[Date] ), -23 ),
        EDATE ( MAX ( 'Calendrier'[Date] ), -12 )
    )
)

(3) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

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

[Jack_Reacher]

Hello @v-tangjie-msft , sorry the late response, I tested many times but still getting the undersider result please find below the link for the Power BI file related to this issue: https://drive.google.com/file/d/1-u6GF_cygXVfBhzMIRmdOVlZWlfk-_Xt/view?usp=share_link

This is an example of the desired outcome: 

[v-tangjie-msft]

Hi @Jack_Reacher ,

Please try:

AAAM_Current_R12M = 

CALCULATE(SUM('Sales deployed per full month'[Sales Deployed]),FILTER(ALL(DateTable),[Date]>=EOMONTH(MAX('DateTable'[Date]),-11)&&[Date]<=EOMONTH(MAX('DateTable'[Date]),0)))

AAAM_Prior_R12M = 


CALCULATE(SUM('Sales deployed per full month'[Sales Deployed]),FILTER(ALL(DateTable),[Date]>=EOMONTH(MAX('DateTable'[Date]),-23)&&[Date]<=EOMONTH(MAX('DateTable'[Date]),-11)))

Best Regards,

Neeko Tang

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

[Jack_Reacher]

hello @v-tangjie-msft , unfortunately that is not the correct output graph I want. this is what the end results should look like, which I solved recently: 

Now I just need to be able to sort by Month and Year in this format April 2022, etc. which I can't figure out yet. Any help is much appreciated. this is the file Google Drive, Power BI file used 

[Jack_Reacher]

Thank you for your reply @v-tangjie-msft , I will test and revert back to you. It seems like your results still shows big numbers, and I simply wanna show DDep values. I will share with you a simplied BI file too.