Table visual-How to create a measure to filter data only from second latest month?

[Anonymous]

Hi,

I have a dataset looks like this:

Reporting Month	Team	Predictability
Feb-22	Team1	0.23
Feb-22	Team2	1.10
Jan-22	Team1	0.30
Jan-22	Team2	0.15

And I would like to create a table visual that has 3 columns (Teams, Predictability from latest month, Predictability from second latest month). Since now we are in March 2022, we usually do reporting on data from the last calendar month (Feb 2022 in this case). So in my case, the latest month would be Feb-2022. and Jan-2022 would be considered as the second latest month, and I need a table to report the predictability from both months.

Team	Predictability from latest month	Predictability from second latest month
Team1	0.23	0.30
Team2	1.10	0.15

So for" Predictability from latest month", I managed to create a measure that only shows Predictability from the latest Reporting Month:

CALCULATE (
        SUM ('Table'[Predictability]),

         'Table'[Reporting Month]

             =(MAX('Table'[Reporting Month]))

)

And it works, but I am having trouble getting the correct measure for "Predictability from second latest month", in the measure formula, I've tried PreviousMonth(Table[Reporting Month]), and (MAX('Table'[Reporting Month]), -1), neither of these two works...

Much appreciated if anyone can show me the correct way to create measure for "second latest month". I've looked up a lot of post about "last month" calculation, but couldn't get the right answer still.

[VahidDM]

Hi @Anonymous 

Try these measures:

Predictability from latest month = 
VAR _Today =
    TODAY ()
VAR _FirstDate_This_Month =
    DATE ( YEAR ( _Today ), MONTH ( _Today ), 1 )
VAR _Last_Month_Last_Date = _FirstDate_This_Month - 1
VAR _Last_Month_First_Date =
    DATE ( YEAR ( _Last_Month_Last_Date ), MONTH ( _Last_Month_Last_Date ), 1 )
RETURN
    CALCULATE (
        SUM ( 'Table'[Predictability] ),
        FILTER ( 'Table', 'Table'[Reporting Month] = _Last_Month_First_Date )
    )

Predictability from second latest month = 
VAR _Today =
    TODAY ()
VAR _FirstDate_This_Month =
    DATE ( YEAR ( _Today ), MONTH ( _Today ), 1 )
VAR _Last_Month_Last_Date = _FirstDate_This_Month - 1
VAR _Last_Month_First_Date =
    DATE ( YEAR ( _Last_Month_Last_Date ), MONTH ( _Last_Month_Last_Date ), 1 )
VAR _Last_2Month_Last_Date = _Last_Month_First_Date - 1
VAR _Last_2Month_First_Date =
    DATE ( YEAR ( _Last_2Month_Last_Date ), MONTH ( _Last_2Month_Last_Date ), 1 )
RETURN
    CALCULATE (
        SUM ( 'Table'[Predictability] ),
        FILTER ( 'Table', 'Table'[Reporting Month] = _Last_2Month_First_Date )
    )

Output:

Sample file attached.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[Anonymous]

Hi @VahidDM ,

Many thanks to your prompt reply and much appreciated for it!

I've tested the two measures (one for predictability from latest month, and the second one for predictability from 2nd latest month), they do work and reproduce the output that I want based on the current data I have.

There is one more thing I would like to ask you, I think the measures are created based on the assumption that the months are following in current calendar system. E.g. it assumes that the latest month is the last calendar month, and the 2nd latest month is the 2nd last calendar month- which works perfectly for my current data. But lets say there is a scenario when Feb data was not given, and right now we are in March, that means I will have to treat Jan-22 as the latest month, and Dec-21 as the 2nd latest month.

I am wondering if there is a way to modify the measures so that the month with the latest data in the dataset(e.g, the latest data is from Jan-22 or Nov-21 or any month before Mar-22, and now we are in Mar-22) can be treated as the "latest month" (not last calendar month as the latest month), and the month with the 2nd latest data given in the dataset(e.g. the second latest data is from Dec-21 etcs) can be treated as the "2nd latest month". Sorry I didn't explain this clearly in my original post, hope this makes more sense.

Thanks again for your help @VahidDM 

[Whitewater100]

Hi:

If you could add a calulated column to your Date Table:

Running Month Index =

VAR minyear = YEAR(MIN(Dates[Date]))

VAR thisyear = YEAR(Dates[Date])

Return

(thisyear - minyear) * 12 + MONTH(Dates[Date])

Then it's easier to add or subtract:

=CALCULATE (
        SUM ('Table'[Predictability]), FILTER(ALL(Dates), 
        Date[Month Index] = MAX(Date[Month Index] - 1))

and you can do this for two months ago.

You may want to add a month complete and throw that into your filter pane, set to TRUE.

Like so:

Completed Month  =

      VAR ThisMonthID =

              LOOKUPVALUE(Dates[Index], Dates[Date], TODAY())

                   RETURN Dates[Index] < ThisMonthID

          //returns TRUE() if the month is in the past and complete

THEN filter your reports -charts by bringing this field in and check “Completed Month = TRUE