cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TheHans
Helper I
Helper I

Display only information from the latest day of the week where data is available

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

TheHans_0-1618644394900.png

 

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:

TheHans_1-1618644559505.png 

 

The column "Date" is mapped with the column "Report Date" of the  reporting data which comes like this:

TheHans_3-1618644644441.png

 

The problem that I have is that in my visual ALL days per week are considered and not just the latest one.

TheHans_4-1618644785760.png

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

 

 

1 ACCEPTED SOLUTION
Jihwan_Kim
Community Champion
Community Champion

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 

 

Group by maxdate Issue =
VAR currentstatus =
MAX ( Data[Status] )
VAR maxdateissue =
FILTER (
ADDCOLUMNS (
GROUPBY (
CALCULATETABLE ( Data, ALLSELECTED ( Data[Status], Data[Issue Key] ) ),
Data[Issue Key],
"@maxdate", MAXX ( CURRENTGROUP (), Data[Report Date] )
),
"@status", SELECTEDVALUE ( Data[Status] )
),
[@status] = currentstatus
)
VAR statustablefilter =
FILTER (
CALCULATETABLE ( Data, ALLSELECTED ( Data[Status], Data[Issue Key] ) ),
Data[Status] = currentstatus
&& Data[Report Date] = MAXX ( maxdateissue, [@maxdate] )
)
RETURN
COUNTROWS ( statustablefilter )
 

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/

 

Picture1.png

View solution in original post

14 REPLIES 14
TheHans
Helper I
Helper I

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. 

TheHans_2-1618903762466.png

 


TheHans_0-1618901592945.png

 

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?

 

TheHans_1-1618903373636.png

When I create a measure it appears within a different table.

Thanks in advance, I really appreciate your support.

Hans

 

Jihwan_Kim
Community Champion
Community Champion

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/

 

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

Jihwan_Kim
Community Champion
Community Champion

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 

 

Group by maxdate Issue =
VAR currentstatus =
MAX ( Data[Status] )
VAR maxdateissue =
FILTER (
ADDCOLUMNS (
GROUPBY (
CALCULATETABLE ( Data, ALLSELECTED ( Data[Status], Data[Issue Key] ) ),
Data[Issue Key],
"@maxdate", MAXX ( CURRENTGROUP (), Data[Report Date] )
),
"@status", SELECTEDVALUE ( Data[Status] )
),
[@status] = currentstatus
)
VAR statustablefilter =
FILTER (
CALCULATETABLE ( Data, ALLSELECTED ( Data[Status], Data[Issue Key] ) ),
Data[Status] = currentstatus
&& Data[Report Date] = MAXX ( maxdateissue, [@maxdate] )
)
RETURN
COUNTROWS ( statustablefilter )
 

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/

 

Picture1.png

View solution in original post

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:

TheHans_0-1618932973445.png

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

Jihwan_Kim
Community Champion
Community Champion

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.

 

Picture1.png

 

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

Jihwan_Kim
Community Champion
Community Champion

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 

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

Jihwan_Kim
Community Champion
Community Champion

Hi, @TheHans 

Yes, you are correct. 

 

Jihwan_Kim
Community Champion
Community Champion

Hi, @TheHans 

Please check the below picture and the sample pbix file's link down below, whether it is what you are looking for.

 

Picture1.png

 

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/

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

Jihwan_Kim
Community Champion
Community Champion

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/

@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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors