Calculate Previous Month Value from Dynamic TTM Measure

[BunnyV]

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.

[v-shex-msft]

HI @BunnyV ,

It seems like you are calculate rolling across multiple date fields, can you please share some dummy data for test? 

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[BunnyV]

@v-shex-msft @Greg_Deckler @amitchandak Thanks for all of your replies.

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

Month	VALUE
Jan-18	1194
Feb-18	1103
Mar-18	1313
Apr-18	1289
May-18	1363
Jun-18	1317
Jul-18	1518
Aug-18	1560
Sep-18	1474
Oct-18	1420
Nov-18	1487
Dec-18	1526
Jan-19	1437
Feb-19	1459
Mar-19	1650
Apr-19	1470
May-19	1699
Jun-19	1421
Jul-19	1591
Aug-19	1549
Sep-19	1517
Oct-19	1275
Nov-19	1417
Dec-19	1428
Jan-20	197

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.

[BunnyV]

@v-shex-msft @Greg_Deckler @amitchandak 

Can you guys please help me with this.

Thanks,

Bunny

[v-shex-msft]

Hi @BunnyV ,

Maybe you can try to add an if statement to filter current and previous year based don selected calendar date:

_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
        )
    )
VAR cYear =
    YEAR ( MAX ( calndar[Date] ) )
RETURN
    IF ( YEAR ( CurrentDate ) IN { cYear - 1, cYear }, Result / MonthNo )

Notice: add a visual filter to hide records who not contain correspond measure results.

Regards,

Xiaoxin Sheng

[amitchandak]

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-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[BunnyV]

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

[Greg_Deckler]

See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...