Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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

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 li...
VahidDM's avatar
VahidDM
Icon for Super User rankSuper User
4 years ago

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's avatar
    Anonymous
    Not applicable
    4 years ago

    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