topic Re: Display only information from the latest day of the week where data is available in DAX Commands and Tips

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

TheHans — Sat, 17 Apr 2021 07:35:06 GMT

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

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

Jihwan_Kim — Sat, 17 Apr 2021 09:52:05 GMT

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/

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

TheHans — Sun, 18 Apr 2021 07:14:23 GMT

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

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

Jihwan_Kim — Sun, 18 Apr 2021 07:48:18 GMT

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/

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

TheHans — Sun, 18 Apr 2021 18:59:42 GMT

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

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

TheHans — Tue, 20 Apr 2021 07:30:07 GMT

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

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

Jihwan_Kim — Tue, 20 Apr 2021 07:55:02 GMT

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/

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

TheHans — Tue, 20 Apr 2021 08:08:04 GMT

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

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

Jihwan_Kim — Tue, 20 Apr 2021 11:12:18 GMT

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/

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

TheHans — Tue, 20 Apr 2021 15:36:50 GMT

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

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

Jihwan_Kim — Tue, 20 Apr 2021 20:13:12 GMT

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.

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

TheHans — Wed, 21 Apr 2021 06:29:26 GMT

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

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

Jihwan_Kim — Wed, 21 Apr 2021 07:06:12 GMT

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

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

TheHans — Wed, 21 Apr 2021 08:03:21 GMT

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

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

Jihwan_Kim — Wed, 21 Apr 2021 08:18:07 GMT

Hi, TheHans

Yes, you are correct.