How to have a right subtotal of a measure which calcultate variation between 2 period

[Lejouquard]

Hi Guys,

Need your help please on this topic.

In my data base I have sales cumulated amount by month :

My objective is to calculate the sales amount on each period. For that, I have created 2 measures :

> Measure 1 : calculation of the cumulated amount with the function PREVIOUSMONTH

> Measure 2 : calculation of the variation : 'sales cumulated amount' - 'Sales cumulated amount M-1'

The figures by month are ok BUT the total of my variation is wrong : 

How I can fix this , and have a right total of my measure 2 ?

Thx for your help!

[Portrek · Accepted solution]

You can use the measure

MEASURE =
CALCULATE (
SUM ( YourTable[collum] ),
FILTER (
ALL ( YourTable),
yourtable[data] <= MAX ( yourtable[data] )
)
)

See you.

[vmakhija]

@Lejouquard 

You might want to treat totals separately from individual months in your measure definition.

For that, I recommend using HASONEVALUE and ISINSCOPE functions.

Go through the below link to understand how these 2 works -

https://www.sqlbi.com/articles/distinguishing-hasonevalue-from-isinscope/