compute difference between 2 month + highlight the news and the removed rows

[Anonymous]

Hi,

2 questions in one post. Board+Dataset attached

The CRM of my company provides me each month, in Excel format, a statement of active licenses of several products.
Ex: 

id	name		productCode	creationDate	price
13	Customer 9	Product 000	16/11/2020		600
12	Customer 8	Product 001	12/11/2020		500
11	Customer 7	Product 001	12/11/2020		500
10	Customer 6	Product 000	14/09/2020		300
9	Customer 6	Product 001	14/09/2020		600
8	Customer 5	Product 001	31/08/2020		200
7	Customer 5	Product 001	31/08/2020		200
6	Customer 5	Product 000	31/08/2020		200
5	Customer 4	Product 001	17/10/2019		400
4	Customer 3	Product 001	12/03/2019		450
3	Customer 3	Product 000	08/03/2019		450
2	Customer 2	Product 001	04/01/2019		350
1	Customer 1	Product 001	11/12/2018		300

I aggregate them and I built a report with usefull KPI

Now I'm stucked on 2 issues

1 - How compute the difference of the values between the current month and the previous one ?
Ex: 1300 between May and April

I played with DAX functions but without success so far

2 - How to display on a table the news and the removed lines for the N lastest months?
ex: news deals (win) and the lost one, for the last 3 months

Status	id	Name			Date		Difference
Win		18	Customer 12		may-21		+600
Win		17	Customer 7		may-21		+500
Win		16	Customer 11		april-21	+500
Lost	12	Customer 8		april-21	-500
Win		15	Customer 4		march-21	+600
Lost	6	Customer 5		march-21	-200
Lost	2	Customer 2		march-21	-350
Lost	1	Customer 1		march-21	-300

Thanks

[v-deddai1-msft]

Hi @Anonymous ,

For the first case, you need to create a new date table and create the relationship between your fact table and the date table:

Table = ADDCOLUMNS(CALENDARAUTO(),"Monthyear",FORMAT([Date],"mmm")&"-"&FORMAT([Date],"yy"))

Then you can use the following measure:

Measure = var MTDSales = CALCULATE(SUM(powerBI[price]),DATESMTD('Table'[Date])) var lastMTDSales = CALCULATE(SUM(powerBI[price]),DATESMTD(DATEADD('Table'[Date],-1,MONTH))) return MTDSales-lastMTDSales 

You can refer to the pbix file. 

For your second case, I don't understand your logic , would you please explain more about it ？

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

Best Regards,

Dedmon Dai

[amitchandak · Accepted solution]

@Anonymous , we usually use time intelligence for that with date table 

example

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

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

[Anonymous]

Thanks you solve my first case 🙂

But I believe there is a lack of efficiency on the way I implement it because I use 3 measure to get the result:

1 - MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
2 - last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

3 - Result = MTD Sales - last MTD Sales

No way to do this in only one step?