Calculating Deltas with a slicer in Date

[Anonymous]

Hi All,

I am working on a database where I trace the value of our assets on a monthly basis and also calculate the deltas of these values for each month. The basis table would look as follows:

Date	Asset	Value
01-01-2022	Asset XX	1.000
01-01-2022	Asset YY	2.000
01-02-2022	Asset XX	900
01-02-2022	Asset YY	2.100
01-03-2022	Asset XX	950

In principle I use a slicer in the report to only show the latest month (01-03-2022 in this example) and show the Value including the difference in Value with the previous month. However, in below example Asset YY has been disposed of in February. In the current set-up we only see Asset XX being reflected in our visual, but we would like to have the following outcome:

Asset	Value	Difference previous month
Asset XX	950	+50
Asset YY	0	-2.100

Any idea how this could be set-up? Thanks!

[v-yueyunzh-msft · Accepted solution]

Hi , @Anonymous 

According to your description, you want to show the '0' if the select date is no value . Right?

Here are the steps you can refer to :

(1)My test data is the same as yours.

(2)We need to click "New Table" to create a table as the "Slicer":

Slicer = VALUES('Table'[Date])

And we do not need to create relationship between two tables.

(3)We can create two measures:

Value = var _slicer_date= SELECTEDVALUE('Slicer'[Date])
var _current_asset =SELECTEDVALUE('Table'[Asset])
var _t= FILTER(ALLSELECTED('Table') ,MONTH( 'Table'[Date]) =MONTH(_slicer_date) && YEAR('Table'[Date]) = YEAR('Table'[Date])&&'Table'[Asset]=_current_asset)
return 
SUMX(_t,[Value])+0

Difference previos Month = var _slicer_date= SELECTEDVALUE('Slicer'[Date])
var _current_asset =SELECTEDVALUE('Table'[Asset])
var _t= FILTER(ALLSELECTED('Table') ,MONTH( 'Table'[Date]) =MONTH(_slicer_date)-1 && YEAR('Table'[Date]) = YEAR('Table'[Date])&&'Table'[Asset]=_current_asset)
return
[Value]-SUMX(_t,[Value])+0 

(4)Then we can put the 'Slicer'[Date] on the slicer visual and the measures and fields we need on the table and we will meet your need , the result is as follows:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[Anonymous]

Hi Aniya,

Thank you very much for this solution!

Best regards,

Björn

[amitchandak]

@Anonymous , If you need last month value, You can use date table and time intelligence

examples

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

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

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