Forum Discussion

changhongchoo's avatar
changhongchoo
Frequent Visitor
4 years ago
Solved

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. 
 
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    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.

  • changhongchoo's avatar
    changhongchoo
    Frequent Visitor
    4 years ago

    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.


  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.