cancel
Showing results for
Did you mean: Frequent Visitor

## Current & Previous Month Difference Combine within 13 months period

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. 1 ACCEPTED SOLUTION  Community Support

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.

4 REPLIES 4  Community Support

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. Frequent Visitor

Hi @Eyelyn9 ,

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

Regards

Choo Frequent Visitor

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.   Super User

@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.

Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!   