Forum Discussion

BunnyV's avatar
BunnyV
Frequent Visitor
6 years ago

Calculate Previous Month Value from Dynamic TTM Measure

Hello All,

 

I calcualted TTM using Below measure.

 

VAR CurrentDate = MAX(TTM[Date])
VAR PreviousDate = DATE(YEAR(CurrentDate),MONTH(CurrentDate)-11,DAY(CurrentDate))
VAR Result =
CALCULATE(SUM(TTM[Sales]),
FILTER(ALLSELECTED(TTM),
TTM[Date] >= PreviousDate && TTM[Date] <= CurrentDate
))
VAR MonthNo =
CALCULATE(DISTINCTCOUNT(TTM[Month Name]),
FILTER(ALLSELECTED(TTM),
TTM[Date] >= DATE(YEAR(CurrentDate),MONTH(CurrentDate)-11,DAY(CurrentDate)) && TTM[Date] <= CurrentDate
))
RETURN
Result/MonthNo
 
I am able to see the values as i expect.
Now i would like to compare the current month value with previous month value so that i can now how much the difference.
 
In Return Section, i did tried many ways as below but non of them are working.
1:- CALCULATE(Result/MonthNo,FILTER(ALL(TTM), TTM[MonthNumber] = MAX(TTM[MonthNumber])-1 ))
2:- TOTALMTD(REsult/MonthNo,DATEADD(TTM[Date],-1,MONTH))
3:- CALCULATE(Result/MonthNo, PREVIOUSMONTH(Calender[Date]))
4:- 
CALCULATE (Result/MonthNo,FILTER (ALL ( TTM ),TTM[Date] <= SELECTEDVALUE ( ( TTM[Date] ) )))
5:- 
VAR CurrentMonth = LASTDATE(TTM[Date])
VAR LastMonthNum = if(Month(CurrentMonth) = 1, 12, MONTH(CurrentMonth) - 1)
VAR LastMonth = if(Month(CurrentMonth) = 1, DATE(YEAR(CurrentMonth) - 1, LastMonthNum, 1), DATE(YEAR(CurrentMonth), LastMonthNum, 1))
CALCULATE(
    Result/MonthNo,
    ALL('TTM'),
    TTM[Date] >= LastMonth,
    TTM[Date] <= CurrentMonth
)

Can anyone please guide me how i can get the previous month value.
 
Thanks in advance.
Bunny.
 
 

 

General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks

7 Replies

  • BunnyV's avatar
    BunnyV
    Frequent Visitor
    6 years ago

    I guess i got it on my own.

     

    I had to write another measure.

    _PreviousMonth TTM =
    CALCULATE([_TTM],FILTER(ALLSELECTED(TTM),TTM[Date] <= MAX(TTM[Date])-1))
     
    And it works
  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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
    https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions

     

    Then you can use totalmtd or datesmtd

     

    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 MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))

MTD (Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR('Date'[Date])))
MTD (Last Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR(dateadd('Date'[Date],-12,MONTH),"8/31")))


QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))

Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))
Next QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],1,QUARTER)))

Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,Year)))

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date])))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year)))

     

    Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
    Thanks. My Recent Blog -
    https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
    https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739
    https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

     

     

    • BunnyV's avatar
      BunnyV
      Frequent Visitor
      6 years ago

      Anonymous Greg_Deckler amitchandak Thanks for all of your replies.

       

      Please find the below sample data which i am calculating TTM for.

      MonthVALUE
      Jan-181194
      Feb-181103
      Mar-181313
      Apr-181289
      May-181363
      Jun-181317
      Jul-181518
      Aug-181560
      Sep-181474
      Oct-181420
      Nov-181487
      Dec-181526
      Jan-191437
      Feb-191459
      Mar-191650
      Apr-191470
      May-191699
      Jun-191421
      Jul-191591
      Aug-191549
      Sep-191517
      Oct-191275
      Nov-191417
      Dec-191428
      Jan-20197

       

      I have this TTM data table and then, i have create a Calender table which dont have any relation with data table.

      But i am using the YEAR column to filter out the years from Calender as a relative filter using another measure.

      _TTM Measure is as i mentioned in my question, and the relative filter measure which filters last 12 months is

       

      _TTM =
      VAR CurrentDate = MAX(TTM[Date])
      VAR EndDate = MAX(Calender[EOMONTH])
      VAR PreviousDate = DATE(YEAR(CurrentDate),MONTH(CurrentDate)-11,DAY(CurrentDate))
      VAR Result =
      CALCULATE(SUM(TTM[VALUE]),
      FILTER(ALLSELECTED(TTM),
      TTM[Date] >= PreviousDate && TTM[Date] <= CurrentDate
      ))
      VAR MonthNo =
      CALCULATE(DISTINCTCOUNT(TTM[Month]),
      FILTER(ALLSELECTED(TTM),
      TTM[Date] >= DATE(YEAR(CurrentDate),MONTH(CurrentDate)-11,DAY(CurrentDate)) && TTM[Date] <= CurrentDate
      ))
      RETURN
      Result/MonthNo
      ----
      _Last12Months =
      VAR TTMDATE = MAX(TTM[Date])
      VAR CurrentDate = MAX(Calender[Date])
      VAR PreviousDate = DATE(YEAR(CurrentDate),MONTH(CurrentDate)-11,DAY(CurrentDate))
      VAR SelectedYear = SELECTEDVALUE(TTM[Year])
      RETURN
      SWITCH(TRUE(),
      TTMDATE <= CurrentDate && TTMDATE >= PreviousDate,"12Months","Others")

      Output:-
       
      Now the values which are coming are absolute fine, and if you see even though i selected the value from YEAR slicer there is no filter applied onto the table because there is no relation from TTM data table to caleder.
      To filter out last 12 months data accordingly, 
      I put that _Last12Months measure into visual level filter and filteredout the months.
      But then the values are getting changed and the values are coming wrong.
       
      The values which i am getting before filtering 12months are the right values.
       
      Can anyone please guide me where is the issue is.
       
      Thanks,
      Bunny.