Show Opposite to Cumulative VIsual

[GarryPope]

Hello Power BI Community,

I hope you can help please.

I'm new to Power BI so not sure I'm even asking the question correctly or using the right lingo, so apologies in advance. 

I have a visual where I add monthly values to a column, let's call it mycolumnname.

So the values I add are April = 10, May = 5, June = 5, July = 5 etc. 

I have a line chart showing this trend.

I also have a cumulative measure that shows the total numbers so April = 10, May = 15, June = 20, July = 25.

I used the below formula:

Cumulative Measure = CALCULATE(SUM('mytablename'[mycolumnname]) - , filter(ALLSELECTED('mytablename'),'mytablename'[mydatecolumnname]<=max('mytablename'[mydatecolumnname])))

This works perfect... But now for another visual I want to do the opposite of the formula above. I think opposite is the correct word. 

So in mycolumnname I'm now going to have the cumulative values rather than the monthly values, and I want to work out in my measure what each month value is.

The data I will add in mycolumnname is April = 10, May = 15, June = 20, July = 25.

My opposite cumulative measure will work out the monthly change between this month and last month from the values above, so April = 10, May = 5, June = 5, July = 5.

Hope that makes sense? Sorry if it's confusing, but as I said, I don't know the lingo so well. If anyone could help I'd greatly appreciate it. 

Thanks very much,

Garry

[Jihwan_Kim]

Link to the pbix file 

[GarryPope]

Hello @Jihwan_Kim,

Thanks for the reply. This looks like exactly what I want, unfortunately my company won't let me download this file. Could you share the measure formula please here?

Thanks very much,

Garry 

[Jihwan_Kim]

Qty Cumulate Measure : =

VAR _maxmonthnumber =

CALCULATE ( MAX ( Months[Month number] ), ALL () )

RETURN

IF (

HASONEVALUE ( Months[Month] ),

SUM ( Sales[Qty Cumulate] ),

CALCULATE (

SUM ( Sales[Qty Cumulate] ),

FILTER ( ALL ( Months ), Months[Month number] = _maxmonthnumber )

)

)

Qty Monthly Measure : =

VAR _startingperiod =

CALCULATE ( MIN ( Months[Month number] ), ALL () )

VAR _endingperiod =

CALCULATE ( MAX ( Months[Month number] ), ALL () )

VAR _previousmonth =

MAX ( Months[Month number] ) - 1

VAR _previousmonthqtycumulate =

CALCULATE (

[Qty Cumulate Measure :],

FILTER ( ALL ( Months ), Months[Month number] = _previousmonth )

)

RETURN

IF (

MAX ( Months[Month number] ) = _startingperiod,

[Qty Cumulate Measure :],

IF (

HASONEVALUE ( Months[Month] ),

[Qty Cumulate Measure :] - _previousmonthqtycumulate,

CALCULATE (

[Qty Cumulate Measure :],

FILTER ( ALL ( Months ), Months[Month number] = _endingperiod )

)

)

)

[GarryPope]

Hello @Jihwan_Kim,

Thanks so much for sending that. I don't doubt it's correct, but I was unable to format it to my table and columns, so was unable to use it. Sorry. A colleague provided something that works for me.

Cumulative difference =

sum(mytablename[mycolumnname])-CALCULATE(SUM(mytablename[mycolumnname]),PREVIOUSMONTH(mytablename[mycolumndate]))

This works prefectly, but shows a blank value for July whilst we are in July, so I added a filter to hide blanks. Probably not the most elegant way to do it, but it did the trick.

I appreciate your help. Thanks so much. 

Garry

[amitchandak]

@GarryPope , Opposite mean total in April and then reduce?

example measure

Cumulative Measure = CALCULATE(SUM('mytablename'[mycolumnname]) - , filter(ALLSELECTED('mytablename'),'mytablename'[mydatecolumnname]>=min('mytablename'[mydatecolumnname])))

[GarryPope]

Hello @amitchandak,

Thanks so much for the speedy reply.

Sorry for confusing you, I'm confusing myself.

It's not so much reduce as "Give me the difference between the April value: 10 and the May value; 15, which is 5.

So 5 that's the difference between April and May.

I knew the total in April 10 and now I know the total in May 15, so my data has increased by 5 between April and May.

I tried your formula, thanks for sharing, but it doesn't like the comma between the - and filter, see highlighted in red below:

Cumulative Measure = CALCULATE(SUM('mytablename'[mycolumnname]) - , filter(ALLSELECTED('mytablename'),'mytablename'[mydatecolumnname]>=min('mytablename'[mydatecolumnname])))

The error is Unexpected expression ','.

Thanks very much,

Garry