Line graph x-axis hierarchy

[dan_yoxall]

Hi

I have been trying to use a measure to calculate a running total.  Originally I tried the below expression but as you can see from the matrix snapshot below, the calculation started again at the beginning of the each year.

Cost Forecast (cumulative) =

CALCULATE(
          SUMX( 'Programme Resource Planning', 'Programme Resource Planning'[Hours] * 'Programme Resource Planning'[Cost]) * MAX('Fee Factor'[Fee Factor]),
          FILTER(
                      ALLSELECTED('Programme Resource Planning'[Week No]),
                      ISONORAFTER('Programme Resource Planning'[Week No], MAX('Programme Resource Planning'[Week No]), DESC)),
          FILTER(
                     VALUES('Programme Resource Planning'[Year]),
                     ISONORAFTER('Programme Resource Planning'[Year], MAX('Programme Resource Planning'[Year]), DESC)))

I managed to overcome this issue with the below formula:

Cost Forecast (cumulative 2) =
CALCULATE(
          SUMX( 'Programme Resource Planning', 'Programme Resource Planning'[Hours] * 'Programme Resource Planning'[Cost]) * MAX('Fee Factor'[Fee Factor]),
         FILTER(
                     ALL('Programme Resource Planning'), 'Programme Resource Planning'[Date] <= MAX('Programme Resource Planning'[Date])))

The issue I have now is I can't represent the results as a line graph (see below).  The second formula is completely wrong!  The first formula is much closer but adds all the values with the same week even if they are from different years.  I'm hoping this is simply a case of sorting the hierachy on the x-axis to show both the year and week no but I can't work out how.  

[v-yulgu-msft · Accepted solution]

Hi @dan_yoxall,

In your scenario, since you have gotten the correct running values via the second measure [Cost Forecast (cumulative 2)], you only need to modify your date hierarchy and chart visual to make it display as you expected.

Please create hierarchy like this:

Then, click the third drill down icon to locate to Year-WeekNo level.

Best regards,
Yuliana Gu