Create a measure and calculate Month on Month different by Name

[Anonymous]

Hello

Any idea how can I create a measure to show previous month balance per Name?

My aim is to calculate Month on Month difference using a slicer.

Year	Month	Name	Balance
2019	April	Amy	14,043.40
2019	April	Bill	8,761.53
2019	April	Jimmy	10,115.35
2019	May	Amy	5,011.72
2019	May	Bill	22,128.64
2019	May	Jimmy	452.33
2020	April	Amy	24,801.22
2020	April	Bill	737.87
2020	April	Jimmy	7,565.99
2020	May	Amy	26,527.03
2020	May	Bill	2,304.00
2020	May	Jimmy	8,167.68
2021	April	Amy	1,245.68
2021	April	Bill	44.61
2021	April	Jimmy	6,904.36
2021	May	Amy	1,140.34
2021	May	Bill	31.22
2021	May	Jimmy	2,054.26

Heaps thanks,

Jess

[v-yalanwu-msft · Accepted solution]

Hi @Anonymous  ,

You could first create a currentdate column by year and month, and then calculate the Balance of the previous month and the difference of month and month.

First create a column:

Currentdate =DATE ( [Year], MONTH ( CONVERT ( [Month] & " 1", DATETIME ) ), 1 )

Then create a Measure :

Previous Balance =
CALCULATE (
SUM ( [ Balance ] ),
PARALLELPERIOD ( 'Table'[Currentdate], -1, MONTH ),
ALLEXCEPT ( 'Table', 'Table'[Name] ))

And if you want to calculate the difference, you could create another measure :

Difference =
CALCULATE (
SUM ( [ Balance ] ),
PARALLELPERIOD ( 'Table'[Currentdate], 0, MONTH ),
ALLEXCEPT ( 'Table', 'Table'[Name] )
)
- CALCULATE (
SUM ( [ Balance ] ),
PARALLELPERIOD ( 'Table'[Currentdate], -1, MONTH ),
ALLEXCEPT ( 'Table', 'Table'[Name] )
)

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.

[Anonymous]

Hi @v-yalanwu-msft 

You saved my life 😀. Thank you so much.

That's exactly what I need. I think ALLEXCEPT function fits my needs.

You are brilliant.

Thanks,

Jess

[Anonymous]

Hi @amitchandak 

Thanks for your quick response. Don't know why the LM balance didn't work.

Please see below:

In my original data, there only have month year. So, I use power query date to split then I add a column "Calendar date" it did the similar as your Date setting.

From your measure, I can get MTD balance (using your first line) i.e. current month balance

However the last month balance is not working. Tried your line 2 ,please see below but it return nothing.

line 3 measure also return nothing.

The weird thing is when I took out the Sum....[Balance] part, it can locate the last month data.

What am i missing?

Heaps thanks,

Jess

[amitchandak]

@Anonymous , You can create a date like this using month year

Date = "01-" & [Month] & "-" & [year] // Change data type to date

Then use a date table and time intelligence to deal with it

examples

MTD Sales = CALCULATE(SUM(Table[balance]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Table[balance]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Table[balance]),previousmonth('Date'[Date]))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.