Last month total by Category

[wuxxx711]

Hi guys,

I am new in PB. I have one fact table as below,

Category	Item	Date	Total
A	a	1/1/2021	10
A	b	1/1/2021	15
A	c	1/1/2021	20
A	b	2/1/2021	5
A	d	2/1/2021	10
B	e	1/1/2021	20
B	f	1/1/2021	30
B	e	2/1/2021	40
B	g	2/1/2021	50
B	h	2/1/2021	60

I would like the result as below,

Category	Current Month Total(e.g.Feb)	Last Month Total(e.g.Jan)
A	15	45
B	150	50

The would be more Date add in later and more Categories as well.

My question is I want Date as my filter. and how I can calculate "Last Month Total" in this case?

Thanks in advance.

[v-yetao1-msft · Accepted solution]

Hi  @wuxxx711 ,

You can follow these steps

(1)Create a date table and use time intelligence .

Date = CALENDAR(DATE(2021,01,01),DATE(2021,04,01))

(2)Create two measures to sum the current month total and last month total

Current Month Total = SUM('Table'[Total])

Last Month Total = CALCULATE(SUM('Table'[Total]),PREVIOUSMONTH('Date'[Date]))

(3)Create a relationship between ‘Table’ and ‘Date’

(4)Add a visual with  ‘Table’[Category],’Date’[Current Month Total],’Date’[Last Month Total]

The effect is as shown:

Best Regards

Ailsa Tao

[amitchandak]

@wuxxx711 , use time intelligence with date table

example

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

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