cancel
Showing results for
Did you mean:
Frequent Visitor

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

Hi Guys,

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 ?

1 ACCEPTED SOLUTION
Resolver II

You can use the measure

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

See you.

2 REPLIES 2
Resolver II

You can use the measure

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

See you.

Post Prodigy

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/

Announcements