Current & Previous Month Difference Combine within 13 months period

[changhongchoo]

Hi, 

I wanted to calculate the difference between current month and last month, and only show the 13 months trend. The current month can be selected by a slicer.

These are my working steps:
1. Create a current measure:

Current = SUM('Acceptance Point'[value])

2. Create a previous month measure:

Previous =

CALCULATE(

SUM('Acceptance Point'[value]),

PREVIOUSMONTH('Acceptance Point'[end_of_month])

)

3. Filter it to show 13 mths only

Diff =

VAR CurrentDate = Max('Date'[end_of_month])

VAR PreviousDate = DATE(YEAR(CurrentDate),MONTH(CurrentDate)-12,DAY(CurrentDate))

VAR Difference = [Current] - [Previous]

Return

CALCULATE(

Difference,

FILTER(

'Acceptance Point',

'Acceptance Point'[end_of_month] >= PreviousDate && 'Acceptance Point'[end_of_month] <= CurrentDate

)

)

I failed to get the months I wanted to present. Attached the result from my above working. Assume my slicer value is now "Jul-21", I want to show the result from "Jul-20" to "Jul-21" only. 

[v-eqin-msft · Accepted solution]

Hi @changhongchoo ,

From this:

Assume my slicer value is now "Jul-21", I want to show the result from "Jul-20" to "Jul-21" only. 

I think you want to display the value of last 13 month not the total difference of the last 13 month,right?

If so , please create a flag measure and apply it to visual-filter pane, set as "=1":

Flag = 
var _sele=MAX('Date(for slicer)'[Date].[Date])
var _datediff=DATEDIFF(MAX('Table'[Date]),_sele,MONTH)
return IF( _datediff>=0 && _datediff<13,1,0)

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

[changhongchoo]

Hi @v-eqin-msft ,

Thank you for the solution, I have solved the issue by following another reference.

https://www.youtube.com/watch?v=d8Rm7dwM6gc&t=1079s

Regards

Choo

[changhongchoo]

Hi,

I followed the exact steps but the result is blank..I have marked the date calendar as data table and join with the fact table.

[amitchandak]

@changhongchoo , With help from date table try measures like

Current = SUM('Acceptance Point'[value])

Previous =
CALCULATE(
SUM('Acceptance Point'[value]),
PREVIOUSMONTH('Date'[Date])
)

Last 13 month diff =
var _max = maxx(allselected('Date'), 'Date'[Date])
var _min = eomonth(_max,-14)+1
return
calculate( [Current] - [Previous], filter('date', 'Date'[Date] >=_min && 'Date'[Date] <=_max ))

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.