DAX Measure to display trends for Project Status for last 2 months

[SDM_1997]

Hi All,
Need help regarding creating DAX Measure. 
I have my data in the following format.

The Reporting Date is created by concatenating the Month and Year column and then converting to date type. Both Month and Year are in text format. Also the "Overall Status Code" is created by substituting Grey = 1; Red = 2; Green = 3. (Grey values are present in my actual dataset).
My requirement is to show the trend of Project Status in the last 2 months only. It is April now, so ,we have data till March only.
The expected output is something like this:

                                           (So, a matrix visual needs to be used)
Now, I have created a measure for this: -

Trend_last_2_months = 
VAR last_month = CALCULATE(MAX('TableNew'[Overall Status Code]), 'TableNew'[Reporting Date] = EOMONTH(TODAY(),-2)+1)
VAR month_before_last_month = CALCULATE(MAX('TableNew'[Overall Status Code]), 'TableNew'[Reporting Date] = EOMONTH(TODAY(),-3)+1)
VAR data =  IF(last_month = 1 || month_before_last_month = 1, "Not Applicable",
                IF(last_month = month_before_last_month, "No trend",
                    IF(last_month < month_before_last_month, "Downgrade",
                        IF(last_month > month_before_last_month, "Improvement",
                            ""))))
RETURN
data

I am getting the expected output as well. But in a separate table visual like this: -

So, I keep the 2nd table aligned with the matrix visual to show the project, the data is indicating to. But it is separate. If I am adding the measure in the matrix visual directly, I am getting incorrect data and also all 12 months are being shown eventhough, no other months are available in the dummy data model itself as shown in the 1st screenshot.
It looks like this: - 

Definitely not the requirement. The measure is applying for all months separately, which is expected since the mesaure is added to the 'Values' section of the matrix along with the Overall Status but also generating incorrect values.
So, need help in: - 
1. Is it possible to make changes in the measure to have it added to the matrix visual directly and get the expected output?
2. I don't think keeping as a separate visual is an option since Power BI do not have a sync visual scroll bar option so that users can scroll both at once. What can be the options here?
3. Is the measure ok for this requirement? I am a DAX newbie, so not sure if this is how the measure should be written for this purpose?

Please help
Thanks!

[johnt75 · Accepted solution]

Your measure is close, I think it just needs a few tweaks.

Trend_last_2_months =
VAR CurrentDate =
    MAX ( 'Date'[Date] )
VAR last_month =
    CALCULATE (
        MAX ( 'TableNew'[Overall Status Code] ),
        'TableNew'[Reporting Date]
            = EOMONTH ( CurrentDate, -2 ) + 1
    )
VAR month_before_last_month =
    CALCULATE (
        MAX ( 'TableNew'[Overall Status Code] ),
        'TableNew'[Reporting Date]
            = EOMONTH ( CurrentDate, -3 ) + 1
    )
VAR data =
    IF (
        last_month = 1
            || month_before_last_month = 1,
        "Not Applicable",
        IF (
            last_month = month_before_last_month,
            "No trend",
            IF (
                last_month < month_before_last_month,
                "Downgrade",
                IF ( last_month > month_before_last_month, "Improvement" )
            )
        )
    )
RETURN
    data

You don't want to pick the months based on TODAY, you want them based on the date from the matrix visual. That is what the MAX('Date'[Date]) is doing.

Secondly, you don't want to return an empty string in the final else clause, you want to return blank which is the default value. Power BI will automatically strip out blank values so won't show data for invalid month-project combinations.

[SDM_1997]

Hi John,
Thank you again for your help!
Got your point, using the date column used in the Matrix visual as current date would restrict the data till the latest month only.
But it looks like the actual expected output won't be possible then? 

Since adding the measure in the value section of matrix visual would mean it would still show the measure alongside each month separately, instead of at last.

Is there any workaround for this? Or we have to use a separate table visual for this only?
Thanks!

[johnt75]

I don't see a way to just have it at the end, I think you will need a separate table I'm afraid.

[SDM_1997]

Okay no problem!
Just have to convince the client 😁.

Thanks again !!