Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Cumulative = CALCULATE ( COUNT ( 'HRdata'[ID] ); FILTER ( 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]; SAMEPERIODLASTYEAR('Calendar'[Date]); ALL('Calendar'))
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?
Solved! Go to Solution.
Was able to solve this by using the SAMEPERIODLASTYEAR for counting the last year values first:
CountOfID LY = CALCULATE( [CountOfID]; SAMEPERIODLASTYEAR('Calendar'[Date]); ALL('Calendar'))
And then just turned it into cumulative the same way cumulative of selected year.
Cumulative LY = CALCULATE( [CountOfID LY]; FILTER( ALLSELECTED('Calendar'); 'Calendar'[Date] <= MAX('Calendar'[Date]) ) )
Still no idea why my first solution didn't work but this was the workaround.
I have been fighting with this problem for the last four days. Your workaround works. I don't know why but it does. thanks for posting.
Was able to solve this by using the SAMEPERIODLASTYEAR for counting the last year values first:
CountOfID LY = CALCULATE( [CountOfID]; SAMEPERIODLASTYEAR('Calendar'[Date]); ALL('Calendar'))
And then just turned it into cumulative the same way cumulative of selected year.
Cumulative LY = CALCULATE( [CountOfID LY]; FILTER( ALLSELECTED('Calendar'); '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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
98 | |
78 | |
65 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |