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
Highlighted
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.

 

Highlighted
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.

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 182 members 2,276 guests
Please welcome our newest community members: