Measure to display Project IDs based on data in 2 columns.

[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.
My requirement is to display all the Project IDs where the Overall Status has remained "Red" for the last 3 months (March ,February and January in this case). Currently this is April, so we have data till March only. In May ,April's data is received.
So, in this case, only 'ID-1' will be the output since last 3 months were all Red.
The expected output is something like this:

How to write a measure for this?
I am trying for DAX measure because many calculated columns affect refresh perfromance. So, I want to avoid that unless a measure is not possible here.
Please help. 
Thanks! 

[johnt75 · Accepted solution]

The below should limit the measure to only show values when there are 3 entries,

Red for 3 months =
VAR StartDate =
    EOMONTH ( TODAY (), -4 ) + 1
VAR EndDate =
    EOMONTH ( TODAY (), -1 )
VAR Statuses =
    CALCULATETABLE (
        VALUES ( 'Table'[Overall status] ),
        DATESBETWEEN ( 'Table'[Reporting date], StartDate, EndDate )
    )
VAR NumDates =
    COUNTROWS (
        CALCULATETABLE (
            VALUES ( 'Table'[Reporting date] ),
            DATESBETWEEN ( 'Table'[Reporting date], StartDate, EndDate )
        )
    )
RETURN
    IF ( COUNTROWS ( Statuses ) = 1 && "Red" IN Statuses && NumDates = 3, 1 )

I'm not sure what you mean in your second question. A DAX calculation can use as many columns as it needs to.

[johnt75 · Accepted solution]

That measure is returning a table of values, so it needs to be used where a table is expected rather than a scalar value. If you are trying to exclude older data from your table visual you could create a measure like

Reporting Dates is in last 3 Months =
VAR StartDate =
    EOMONTH ( TODAY (), -4 ) + 1
VAR EndDate =
    EOMONTH ( TODAY (), -1 )
RETURN
    IF (
        SELECTEDVALUE ( 'Table'[Reporting date] )
            IN DATESBETWEEN ( 'Table'[Reporting date], StartDate, EndDate ),
        1
    )

 and then use that as a visual level filter to only show where the value is 1.

[johnt75]

Create a measure like

Red for 3 months =
VAR StartDate =
    EOMONTH ( TODAY (), -4 ) + 1
VAR EndDate =
    EOMONTH ( TODAY (), -1 )
VAR Statuses =
    CALCULATETABLE (
        VALUES ( 'Table'[Overall status] ),
        DATESBETWEEN ( 'Table'[Reporting date], StartDate, EndDate )
    )
RETURN
    IF ( COUNTROWS ( Statuses ) = 1 && "Red" IN Statuses, 1 )

and then use that as a visual level filter to only show when the value is 1

[SDM_1997]

Hi!
Thank you for the reply.
I used your measure and as a DAX newbie, I have a few doubts to clarify.
Please refer to the screenshot below:

I created a table visual and added the measure in the visual level filter. But the thing is as soon as I add the Overall Status field, it is also showing the PRJ-2 value, since it has 2 Red values. Can you suggest a fix such that only the projects which have 3 Reds in the consecutive 3 months will be shown? So, PRJ-2 will be omitted.
Then, another doubt is, Did you create a calculated table because 2 columns needs to be used for calculation? Since, to create a normal DAX measure, calculation needs to be using 1 column only?  
Thanks !

[johnt75 · Accepted solution]

The below should limit the measure to only show values when there are 3 entries,

Red for 3 months =
VAR StartDate =
    EOMONTH ( TODAY (), -4 ) + 1
VAR EndDate =
    EOMONTH ( TODAY (), -1 )
VAR Statuses =
    CALCULATETABLE (
        VALUES ( 'Table'[Overall status] ),
        DATESBETWEEN ( 'Table'[Reporting date], StartDate, EndDate )
    )
VAR NumDates =
    COUNTROWS (
        CALCULATETABLE (
            VALUES ( 'Table'[Reporting date] ),
            DATESBETWEEN ( 'Table'[Reporting date], StartDate, EndDate )
        )
    )
RETURN
    IF ( COUNTROWS ( Statuses ) = 1 && "Red" IN Statuses && NumDates = 3, 1 )

I'm not sure what you mean in your second question. A DAX calculation can use as many columns as it needs to.

[SDM_1997]

Hi !
Thank you for this. I am able to see only the ID which has 3 Red values now.
My bad, in my 2nd question actually I was confused between the CALCULATETABLE function that you used with the creating a calculated table option that we have in PBI Desktop. Hence the confusion. It is clear now.
Also can you explain, when I am using the Reporting Date column in the visual, I am able to see the data. Like this: 

But as soon as I change it to Date Hierarchy, the visual goes blank. 

Thanks again ! 

[johnt75]

I think if you wanted to use the date hierarchy you would need to change the code, change every mention of 'Table'[Reporting date] to 'Table'[Reporting date].Date or 'Table'[Reporting date].[Date], not sure which.

[SDM_1997]

Hi !
Yes it is .[Date], the date hierarchy is working.
I currently tried the Measure in my actual dataset now.
It is working but there is one thing.
So, since the Reporting Date column actually has many months of data, so, bringing the 'Table'[Reporting Date] in the visual shows all the previous data as well. So, basically data prior to the latest 3 months, where different values like Green or Gray maybe present.
I tried to create a new measure which will filter the reporting date to 3 months only using your DATESBETWEEN function usage and then bring that measure in the [Red for 3 Months] measure. Like this: 

Reporting Dates in 3 Months =
VAR StartDate =
    EOMONTH ( TODAY (), -4 ) + 1
VAR EndDate =
    EOMONTH ( TODAY (), -1 )

      RETURN 
            DATESBETWEEN ( 'Table'[Reporting date], StartDate, EndDate )

 Then after I replaced this [Reporting Dates in 3 Months] with all the DATESBETWEEN used in [Red in 3 Months], got an error "A function 'PLACEHOLDER' has been used in a True/False expression that is used as a table filter expression. This is not allowed".

Can you point out where the mistake is happening? 
Thanks!

[johnt75 · Accepted solution]

That measure is returning a table of values, so it needs to be used where a table is expected rather than a scalar value. If you are trying to exclude older data from your table visual you could create a measure like

Reporting Dates is in last 3 Months =
VAR StartDate =
    EOMONTH ( TODAY (), -4 ) + 1
VAR EndDate =
    EOMONTH ( TODAY (), -1 )
RETURN
    IF (
        SELECTEDVALUE ( 'Table'[Reporting date] )
            IN DATESBETWEEN ( 'Table'[Reporting date], StartDate, EndDate ),
        1
    )

 and then use that as a visual level filter to only show where the value is 1.

[SDM_1997 · Accepted solution]

Hi John,

Just to add to this, restricting data to show for the latest 3 months only for which we were comparing data for.
Found this setting in the visual level filter to dynamically show only the last 3 months of data.

So, no need for a separate measure and this actually makes the visual load faster as well.
Just adding it here for others to refer in future.
Thanks !!

[SDM_1997]

Hi John,

Thank you very much for your help !!
A lot of my problems and doubts were cleared.

Happy Weekend !