Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Guys,
I have a table with an issue tracking which I would like to visualize in a stacked bar chart, in cluding the history over past calendar weeks. The visualization shows the amount of issues grouped by status per calendar week
The issues are not updated regularly, it could be that in one week the data is updated on two days and in the other week on 5 days. However, in the chart I would like to see only the amount of issues from the last updated day.
In order to get the calendar weeks I've crated a helper table using DAX:
The column "Date" is mapped with the column "Report Date" of the reporting data which comes like this:
The problem that I have is that in my visual ALL days per week are considered and not just the latest one.
I need your help in order to find out how to consider only the latest values from one week. I guess I need some helper column or something like that?
Appreciate your support
Cheers
Hans
Solved! Go to Solution.
Hi, @TheHans
Thank you for your feedback.
I tried to write in a different way.
Please check the below whether it works or not.
https://www.dropbox.com/s/wo8irm15d2xsbt2/thehans.pbix?dl=0
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: https://www.linkedin.com/in/jihwankim1975/
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi @Jihwan_Kim ,
I checked it out. It works already pretty good. I just found one thing which does not yet work correctly.
I answered already yesterday to this post but somehow my answer is gone. I had difficulties with some "invalid HTML warnings". Couldn't find it again this morning. So I try again.
I found one case where issues would be counted more than once. I was able to reproduce this also in our example by modifying the input data accordingly.
On 3rd of April we have an open issue C which is in progress on 4th of April. The measure counts the open issue C on 3rd of April (since it is the last occurence of "open" in that week) and counts the issue C in progrees on 4th again. So in total we have 5 issues in week 14 instead of 4.
Can you modify the measure in that way, that only the issues on the latest report day within a week is considered?
I have also another question. How did you create that measure so that it appears in an own "are" at the top of the data tables?
When I create a measure it appears within a different table.
Thanks in advance, I really appreciate your support.
Hans
Hi, @TheHans
Please correct me if I wrongly understood your question.
I think, in my sample calendar table, the week number for each date is different than your standard.
mine: 4th Apr = week 14 -> I changed it to week15 in the link down below. Please check if it is correct.
In your measure table, I think you have one column that contains nothing. Once you delete that column, the icon of the table will be changed.
https://www.dropbox.com/s/wo8irm15d2xsbt2/thehans.pbix?dl=0
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: https://www.linkedin.com/in/jihwankim1975/
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi @Jihwan_Kim ,
the 4th of April is in calendar week 14. But I changed another thing in the Data table:
- Issue C on 03.04.2021 = Open
- Issue C on 04.04.2021 = In Progress.
If you do this you can reproduce what I tried to explain. Issue C is counted twice in week 14, once as "open" and once as "in progress".
However it should be counted only once as "in progress".
Han
Hi, @TheHans
Thank you for your feedback.
I tried to write in a different way.
Please check the below whether it works or not.
https://www.dropbox.com/s/wo8irm15d2xsbt2/thehans.pbix?dl=0
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: https://www.linkedin.com/in/jihwankim1975/
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi @Jihwan_Kim ,
works better and better:-)
there is one issue left.
For some reason not all bars have the totals with it, see screenshot:
In order to reproduce this better, I've attached the PBI file.
Link to my file:
https://www.dropbox.com/s/rc0z59kcd0j6oow/test.pbix?dl=0
Best
Hans
Hi, @TheHans
Please correct me if I wrongly understood your question.
I could not find any difference between the two charts.
I think I am missing something.
If you are only talking about the data labels, then I think you can just stretch enough to make each chart wider to see all the data labels like the below picture. I am not sure why your picture showed the difference, but please try to resize each chart.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi @Jihwan_Kim ,
ups, I didn see that. You're right. This was a beginner's mistake:-) Although, it is strange that if you compare weeks 11 and 12 which are the very same numbers and in one week the totals are displayed and in the other week not.
Thank you so much for this solution. I really appreciate your support.
This is for me really hardcore DAX. Would you mind explaining me roughly which steps you made in the code in order to get to this result? Just if you don't mind.
Thanks again
Hans
Hi, @TheHans
Thank you very much for your feedback.
I wrote a brief explanation about each step in the measure.
Please check the below link, and go click the measure. Then you can see the explanation.
It is the same file as yesterday's one, but I included the comment inside the measure.
I hope I wrote well to understand.
https://www.dropbox.com/s/wo8irm15d2xsbt2/thehans.pbix?dl=0
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Thanks @Jihwan_Kim ,
in this code, we don't have a calendar week as a context, right? This measure works for any given context, for example if I filter by month, it would also work.
The context of a week comes via the visual where I choose the calendar week for the Y-axis.
Hans
Hi, @TheHans
Yes, you are correct.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi, @TheHans
Please check the below picture and the sample pbix file's link down below, whether it is what you are looking for.
https://www.dropbox.com/s/wo8irm15d2xsbt2/thehans.pbix?dl=0
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: https://www.linkedin.com/in/jihwankim1975/
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi @Jihwan_Kim ,
thanks a lot for the fast solution. Even if this works for the simple example I provided here, unfortunately it doesent work in my more complex context, where I have some additional filters on the page.
I should have mentioned this in my example.
in my example this would work if I had the information "Which was the last report date in a certain calendar week?". With this, I made it already working, the downside is, that I manually need to check at the end of every week when the last update was and update this manually in my Excel file.
So let me ask in a different way.
Is it possible to enhance the existing "Date" table by an additional column which calculates for every week the latest report update date ("Report date")?
Or create a new table which contains this information?
Hans
Hi, @TheHans
Please check the below link and the measure.
I fixed it to describe the latest information, instead of the last date's information.
If it still does not suit your pbix file, please share your pbix file's link with deleting important information. Then, I can try to look into it to come up with a more accurate measure.
Group by maxdate Issue =
VAR currentstatus =
MAX ( Data[Status] )
VAR maxdateissue =
CALCULATE (
LASTNONBLANK ( Data[Report Date], MAX ( Data[Issue Key] ) ),
FILTER ( ALLSELECTED ( Data[Status] ), Data[Status] = currentstatus )
)
VAR filtertable =
FILTER (
ALLSELECTED ( Data ),
Data[Report Date] = maxdateissue
&& Data[Status] = currentstatus
)
RETURN
COUNTROWS ( filtertable )
https://www.dropbox.com/s/wo8irm15d2xsbt2/thehans.pbix?dl=0
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: https://www.linkedin.com/in/jihwankim1975/
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
@Jihwan_Kim thanks again for the quick reply. I will check this out and let you know.
Need a little bit of time to try it out 🙂
Hans
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
19 | |
18 | |
16 | |
15 |
User | Count |
---|---|
51 | |
26 | |
22 | |
17 | |
16 |