Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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!
Solved! Go to 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.
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.
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.
Thank you for catching my mistake! I have corrected my original post to reflect what I meant to have originally written.
Radek
@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.
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!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
113 | |
105 | |
95 | |
58 |
User | Count |
---|---|
175 | |
147 | |
134 | |
105 | |
82 |