Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Cumulative graph not starting from 0

Hi all,

 

I'm trying to create a cumulative graph that compares revenue at the same point of the year for each of our past financial years. On the X axis I have day of the year (which is actually day of the financial year e.g. 1st July = day 1). I've created the following measure with some help from online:

 

TotalYTD = CALCULATE(SUM(New_Merge[Actual_Rate]), FILTER(ALLSELECTED(New_Merge), AND(New_Merge[Sale Date] <= MAX(New_Merge[Sale Date]), YEAR(New_Merge[Sale Date]) = YEAR(MAX(New_Merge[Sale Date])))))
 
However I cant work out why the lines are still on top of eachother and not all starting from £0 as they should?
 
Any help would be very much appriciated!
 

 

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    7 years ago

    Use following measure according to your columns.

    Cumulative From Start = CALCULATE(SUM(Orders[Total_Sale]), FILTER(ALL(Orders),Orders[Order_date ]
    <=MAX(Orders[Order_date ] )),VALUES(Orders[YEAR]))

     

    also you can use VALUES(Orders[MONTH]) instead of year if required.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Use following measure according to your columns.

    Cumulative From Start = CALCULATE(SUM(Orders[Total_Sale]), FILTER(ALL(Orders),Orders[Order_date ]
    <=MAX(Orders[Order_date ] )),VALUES(Orders[YEAR]))

     

    also you can use VALUES(Orders[MONTH]) instead of year if required.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Anonymous 

       

      Fantastic! This really helped me to work it out. Initially this didn't show what I wanted, however I have now changed the Order Date for 'Day of Year' in the measure and my X axis is now 'Month of Financial Year' which seems to have fixed it! Measure now reads:

       

      CALCULATE(SUM(New_Merge[Actual_Rate]),FILTER(ALL(New_Merge),New_Merge[FY DayOfYear]<=MAX(New_Merge[FY DayOfYear])),VALUES(New_Merge[Year]))
       
      And graph now looks like the attached :) Thank you for your help.
  • v-yuta-msft's avatar
    v-yuta-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Anonymous ,

     

    Generally, you may modify your measure using DAX below and check if it can meet your requirement.

    Result =
TOTALYTD ( SUM ( New_Merge[Actual_Rate] ), ALLSELECTED ( New_Merge ) )

    Community Support Team _ Jimmy Tao

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