I'm trying to create a cumulative line chart which contains two lines:
Line1 = cumulative count
Line2 = cumulative count from previous year
I have data spanning several years and a slicer which I'm using to select one year at a time.
However I can't get previous year cumulative working.
First measure which counts the cumulative of selected year:
COUNT ( 'HRdata'[ID] );
ALLSELECTED( 'Calendar' );
'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
This works just like it should.
Second measure which is supposed to do the same for previous year using the first measure:
Cumulative LY =
CALCULATE ( [Cumulative];
This does not work, it just draws the line as non-cumulative.
If I change "ALLSELECTED" to "ALL" in the first measure the second measure works but both lines start from the cumulative value of previous years before selected year.
What do I need to change to make the Cumulative LY measure work and also start counting correctly from that period?
Go to Solution.
Was able to solve this by using the SAMEPERIODLASTYEAR for counting the last year values first:
CountOfID LY =
And then just turned it into cumulative the same way cumulative of selected year.
Cumulative LY =
'Calendar'[Date] <= MAX('Calendar'[Date])
Still no idea why my first solution didn't work but this was the workaround.
To make this more clear here is an image of current situation produced by my two measures. Red 'Cumulative' line is correct while black 'Cumulative LY' is showing just the values without being cumulative.
Engage and empower students with Power BI!
Continue your learning in our online communities.
Travel to Melbourne and network with thousands of peers!
Check out what's new in the Power BI Community!