cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
changhongchoo
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.PNG
1 ACCEPTED SOLUTION
Eyelyn9
Community Support
Community Support

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)

 

Eyelyn9_1-1634609887992.png

 

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.

View solution in original post

4 REPLIES 4
Eyelyn9
Community Support
Community Support

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)

 

Eyelyn9_1-1634609887992.png

 

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.

View solution in original post

Hi @Eyelyn9 ,

 

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
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.
3.PNG

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

Did I answer your question? Mark my post as a solution! 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!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.