Need help creating a virtual table to find monthly change

[ddbaker]

Hi everyone, I'm trying to create a calculated table using the following DAX formula: 

SUMMARIZE(
    Subscriptions, 
    Subscriptions[id], 
    Subscriptions[Export Date],
    "Previous Month", 
        EOMONTH(Subscriptions[Export Date],-1),
    "MRR", 
        [A: Net MRR], 
    "Previous MRR", 
        CALCULATE(
            [A: Net MRR], 
            PREVIOUSMONTH('Calendar'[Date])
        )
)

The intent is to use it in a measure as a virtual table. I want to create a table that looks like this: 

And have a column that called "Change in MRR" which will subtract the Previous MRR from the Current MRR. I'm having trouble getting the Previous MRR. Any help would be greatly appreciated!

[Icey]

Hi @ddbaker ,

Try this:

VAR t1 =
    SUMMARIZE (
        Subscriptions,
        Subscriptions[id],
        Subscriptions[Export Date],
        "Previous Month", EOMONTH ( Subscriptions[Export Date], -1 ),
        "MRR", [A: Net MRR]
    )
VAR t2 = 
    ADDCOLUMNS (
        t1,
        "Previous MRR",
            SUMX (
                FILTER (
                    t1,
                    [Export Date] = EARLIER ( [Previous Month] )
                        && [id] = EARLIER ( [id] )
                ),
                [MRR]
            )
    )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ddbaker]

@Icey I followed your steps and got a table like this

My new measure now looks like this:

Up/Downgrade MRR Test = 
VAR t1 =
    SUMMARIZE(
        Subscriptions,
        Prices[Business Unit],
        Subscriptions[Customer ID],
        Subscriptions[Export Date],
        "MRR",
        _Measures[MRR],
        "Previous Month", EOMONTH(Subscriptions[Export Date],-1)
    )
VAR t2 =
    ADDCOLUMNS(
        t1,
        "Previous MRR",
        SUMX(
            FILTER(
                t1,
                [Export Date] = EARLIER([Previous Month]) 
                && [Customer ID] = EARLIER([Customer ID]) 
                && [Business Unit] = EARLIER([Business Unit])
            ),
            [MRR]
        )
    )
VAR t3 =
    ADDCOLUMNS(
        t2,
        "Change in MRR",
        [MRR] - [Previous MRR]
    )
RETURN
SUMX(
    t3,
    [Change in MRR]
)

But the measure returns the amount for total MRR. I checked if the measure would calculate the previous month MRR by changing the Returned value to 

SUMX(
    t3,
    [Previous MRR]
)

and that returns 0. So basically my SUMX doesn't recognize the previous month's MRR in the context given. Any ideas on how to fix this?

@mahoneypat any ideas?

[amitchandak]

@ddbaker , Create new table like

New Column =
var _date = eomonth([Export Date],-1)
return
sumx(filter(Table, eomonth([Export Date],0) =_date),[MRR])

For measure refer

Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA