Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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

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

 

 

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous ,

     

    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. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thank you for your reply Anonymous , 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. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi Anonymous ,

         

        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.