cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jukshou Frequent Visitor
Frequent Visitor

DAX Cumulative for a filtered year and previous year

 

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Jukshou Frequent Visitor
Frequent Visitor

Re: DAX Cumulative for a filtered year and previous year

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.

2 REPLIES 2
Jukshou Frequent Visitor
Frequent Visitor

Re: DAX Cumulative for a filtered year and previous year

 

ho0ands.pngTo 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.

 

Jukshou Frequent Visitor
Frequent Visitor

Re: DAX Cumulative for a filtered year and previous year

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.