How to get Current AND Previous month's project status in one table

[Radek_Z]

There are so many questions and answers posted on this topic, yet none of them seem exactly like this, and none of them work for me:

I have to report on monthly project statuses, but I also want to show how each project's status has changed over the past few months.

Each month I append the month's project status submissions from our team to an ever-growing Excel list. Here is a simplified view of the data. The Reporting Month data is actually a full date (2021-Jan is showing up as 2021-01-01), but I have it showing just as YYYY-MMM to keep it clean.

Project Name |	Reporting Month |	Status
Project 1	2021-Jan	Green
Project 2	2021-Jan	Green
Project 3	2021-Jan	Green
Project 1	2021-Feb	Yellow
Project 2	2021-Feb	Green
Project 3	2021-Feb	Green
Project 1	2021-Mar	Red
Project 2	2021-Mar	Yellow
Project 3	2021-Mar	Green

Assuming that we are in the March 2021 reporting period, I would like to generate a table that shows the project Status for this month, last month, and 2 months ago, like this:

Project Name |	This Month's Status |	Last Month's Status |	2 Months ago Status
Project 1	Red	Yellow	Green
Project 2	Yellow	Green	Green
Project 3	Green	Green	Green

I have a complete Date table linked to the Reporting Month column and it works as expected in my other visuals. I tried using variations of the PREVIOUSMONTH formula but always unsuccessful. I believe the solution lies with a DAX measure; I just can't figure it out.

I would also like the table to only show Projects where the Status has changed in the past 2 months. How do I go about removing rows where there are no Status changes? In the example above, I don't want to see Project 3 on the table since it's Green for all 3 months. 

Any ideas? Thanks in advance!

[v-xiaotang · Accepted solution]

Hi @Radek_Z 

you can use matrix, then create a measure like bellow

Measure = 
var _count=CALCULATE(DISTINCTCOUNT('Table'[Status]),ALLEXCEPT('Table','Table'[Project Name]))
return _count

result:

Best Regards,

Community Support Team _ Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[v-xiaotang]

Hi @Radek_Z 

Best Regards,

Community Support Team _ Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[Radek_Z]

Thank you for catching my mistake! I have corrected my original post to reflect what I meant to have originally written.

Radek

[amitchandak]

@Radek_Z , PREVIOUSMONTH will work if you have date and date table. I see only month sample. So if you can create date and use date table you should be able to get status

Last month = CALCULATE(Max(Table[Status]),previousmonth('Date'[Date]))

last to last month = CALCULATE(Max(Table[Status]),previousmonth(dateadd('Date'[Date],-1,MONTH)))

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 :radacad sqlbi My Video Series Appreciate your Kudos.

[Radek_Z]

Hi Amit,

Thank you for the quick reply.

I have tried your proposal but some of the results (depending on Month selection in slicer) do not make any sense. Here is a sample dataset to illustrate the situation, while using the two measures that you suggested. In this case, I am showing just one project over 6 months of Status reporting, where the Status goes from Green to Yellow in the 3rd month of reporting:

Project Name	Reporting Month	Status
Project 1	2020-Dec	Green
Project 1	2021-Jan	Green
Project 1	2021-Feb	Yellow
Project 1	2021-Mar	Yellow
Project 1	2021-Apr	Yellow
Project 1	2021-May	Yellow

Here is the resulting table when I select the Reporting Month from my Slicer to 2021-May or 2021-Apr:

Project Name	Current Status	Last Month Status	2 Months ago Status
Project 1	Yellow	Yellow	Yellow

So far, so good.

But when I select 2021-Mar from the Slicer, here are the results:

Project Name	Current Status	Last Month Status	2 Months ago Status
Project 1	Green		Green
Project 1	Yellow	Yellow

As you can see, there are a number of things wrong here:

1) Project 1 is reported twice instead of being combined across date range

2) Blanks now in some Statuses, in both rows

3) Combination of Yellow and Green for the same project, completely non-sensical result.

When I select 2021-Feb, the result looks correct:

Project Name	Current Status	Last Month Status	2 Months ago Status
Project 1	Green	Green	Green

When I select 2021-Jan, the results are also correct (I am expecting a blank in the 2 Months ago Status as this lies outside the date range and no data exists for that month):

Project Name	Current Status	Last Month Status	2 Months ago Status
Project 1	Green	Green

Finally, when I select 2020-Dec, the results are again incorrect (nothing shows up at all, but I am expecting the Current Status to be Green, and blanks for Last Month and 2 Months ago):

Project Name	Current Status	Last Month Status	2 Months ago Status

Do you have any idea what is happening here? Thank you!