Variance compared to sum of previous date

[Jay_Mitchel]

Hello all, 

Would really appreciate your help.. I have a complex table - but see simplified issue here: 

Date	Quantity
15/05/2021	5
15/05/2021	4
15/05/2021	1
15/05/2021	1
15/05/2021	1
15/05/2021	1
15/05/2021	5
15/05/2021	55
15/05/2021	5
16/06/2021	22
16/06/2021	1
16/06/2021	1
16/06/2021	1
16/06/2021	1
30/07/2021	45
30/07/2021	1
30/07/2021	1
30/07/2021	1
30/07/2021	55
30/07/2021	50
30/07/2021	1
30/07/2021	1

I would like to see a % variance (in a matrix) - which would be as below: 

May	Jun	Jul
78	26	155
	-67%	496%

I would un-select the Month of May - as this month has nothing to compare with.

June is (26-78)/78= -67%

July is (155-26)/26 = +496% 

I have researched everywhere & tried countless Dax - nothing seems to work. Thank you in advance.  

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Jay_Mitchel]

Thank you Ashish. This worked wonderfully!

[Ashish_Mathur]

You are welcome.

[v-yalanwu-msft]

Hi, @Jay_Mitchel ;

You could create a column to show month by date; then create a measure as follows:

a)create a column

Mon = FORMAT([Date ],"mmm")

b)create a measure

variance =
VAR _value =
    CALCULATE (
        SUM ( [Quantity] ),
        FILTER ( ALL ( 'Table' ), MONTH ( [Date ] ) = MONTH ( MAX ( [Date ] ) ) - 1 ))
RETURN
    IF (
        ISINSCOPE ( [Month] ),
        SUM ( [Quantity] ),
        FORMAT ( DIVIDE ( SUM ( [Quantity] ) - _value, _value ), "0%" ))

c)change the subtotals label:

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jay_Mitchel]

Hello, 

Still some kinks to work out, but your method appears perfect. 

Just one question, what is the empty Month column & why do you reference it in your Dax?

[v-yalanwu-msft]

Hi, @Jay_Mitchel ;

Because my dax needs to be based on a column(ISINSCOPE() need), but there is no suitable column in your example, so I created an empty column, if there are other columns, it is fine. Just here for the sake of beauty I created an empty column.
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jay_Mitchel]

Thank you for the help. 🙂