Time based calculations

[MarcUrdang]

Hello

Please can you help me with this .. I have tried many different things but as a new PBI user I am struggling with this:

I need to calculate the variance/difference for a measure from last month (in this case Sept 19) versus same time last year (in this case = Sept 18) I can do this using filters but as you will see in the file attached to this link, 

https://drive.google.com/open?id=1BBtjX2jgsSTA76xgsUM7sn2yFLV4fWmT

that I have a measure showing 50 for last month this year being Sept and the measure for the same month last year showing 30.

I need to show in a card the value 20 as this would be the month this year vs month last year variance. However I would need to build this into a calc as it would have to be dynamic. ie .. next month you would see Oct 19 vs Oct 18.

Thanks so much,

Marc

[v-juanli-msft · Accepted solution]

Hi @MarcUrdang 

I create a simple test file as below:

Create measures

today = TODAY()

last_m_thisyear =
CALCULATE (
    SUM ( Sheet10[value] ),
    FILTER (
        Sheet10,
        Sheet10[date]
            >= DATE ( YEAR ( [today] ), MONTH ( [today] ) - 1, 1 )
            && Sheet10[date] < DATE ( YEAR ( [today] ), MONTH ( [today] ), 1 )
    )
)


last year =
CALCULATE (
    SUM ( Sheet10[value] ),
    FILTER (
        Sheet10,
        Sheet10[date]
            >= DATE ( YEAR ( [today] ) - 1, MONTH ( [today] ) - 1, 1 )
            && Sheet10[date]
                < DATE ( YEAR ( [today] ) - 1, MONTH ( [today] ), 1 )
    )
)


diff = [last_m_thisyear]-[last year]

If today is 2019/11/2

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MarcUrdang]

Hi Maggie 

AMAZING AMAZING AMAZING

It works perfectly

THANK SO MUCH

regards,
Marc