topic Re: Weekly data and slicer in DAX Commands and Tips

Weekly data and slicer

BratKat — Sun, 09 Apr 2023 08:03:42 GMT

Have a graph that uses a Slicer (using between) and a measure to get each week per a column of the dates we reference.

MonthWeek = 'Work Items'[Team Date 01].[Month] & " Week " & WEEKNUM('Work Items'[Team Date 01])add

Works to grab data by week and moving the slider adds or subtracts weeks fron the graph, however once you move to another month, the weeks are out of order and haven't found a way to keep it correctly in the measure or graph. '

For example displaying January data the weeks are correct (sorted ascending)

If you move the slider to another month the weeks become out of order, even if you resort the axis it may not correct the issue.

the slider values format is M/D/YYYY in case that may be causing the issue, any suggestions appreciated, thank you.

Re: Weekly data and slicer

BratKat — Sun, 09 Apr 2023 14:31:49 GMT

Additional information:

The Team Date is from AzDo Work Items as the date of the task per item.

Re: Weekly data and slicer

Anonymous — Tue, 11 Apr 2023 02:05:22 GMT

Hi BratKat

1.Click the MonthWeek column, then select sort by [Team Date 01] column

Then put the MonthWeek column to the visual

Output

2.You can create a measure

e.g

Tooltip=SUM('Work Items'[Team Date 01])

Then put it to the tooltips, and select "..." of the visual and select sort by "Tooltips" and "ascending"

Then, no matter how you filter the slicer, it can appear in increasing order.

Output

Best Regards!

Yolo Zhu

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

Re: Weekly data and slicer

BratKat — Wed, 12 Apr 2023 09:29:53 GMT

Thank you, I tried this however it didn't resolve -

Even if I change the sort method, still off.

Measure created =

Tooltip = SUM('Work Items'[Team Date 01])

Using this measure in prevous screenshots to sort by weeks

MonthWeek = 'Work Items'[Team Date 01].[Month] & " Week " & WEEKNUM('Work Items'[Team Date 01])

Re: Weekly data and slicer

Anonymous — Wed, 12 Apr 2023 09:44:26 GMT

Hi BratKat

Did you put the measure to the tooltip, and you can try the first method I have offered, after testing, it can be work.

Best Regards!

Yolo Zhu

Re: Weekly data and slicer

BratKat — Wed, 12 Apr 2023 10:59:29 GMT

Tried the first method (Click the MonthWeek column, then select sort by [Team Date 01] column) and recieved this message

Yes I added the measure to the tooltip for the graph

Re: Weekly data and slicer

Anonymous — Thu, 13 Apr 2023 07:07:26 GMT

Hi BratKat

Can you show me some sample data of the [Team Date 01] column? Because the two ways can work on my pbix file.

Best Regards!

Yolo Zhu

Re: Weekly data and slicer

BratKat — Thu, 13 Apr 2023 08:23:58 GMT

Example of column

Re: Weekly data and slicer

Anonymous — Thu, 13 Apr 2023 09:43:39 GMT

Hi BratKat

Please try to keep the ascending ranking of the [Team Date 01], then create a calculated column

e.g

Index = COUNTROWS(FILTER('Table',[Team Date 01]<=EARLIER('Table'[Team Date 01])))

Then modify the Tooltip measure.

Tooltips = SUM('Table'[Index])

Then put the measure to the tooltip, then select sort by the measure.

Best Regards!

Yolo Zhu

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

Re: Weekly data and slicer

BratKat — Thu, 13 Apr 2023 14:21:30 GMT

The MonthWeek item is a measure in my data so does not appear in looking at the data in Transform, tried you method but made it worse. Is there a way I can format the date to appear instead of for example. January Week 1 to appear as 1 Week January so I can sort numerically ? Thank you

Re: Weekly data and slicer

BratKat — Thu, 13 Apr 2023 14:27:05 GMT

Its possible may be creating the graph wrong. Am using my MonthWeek measure in the Slicer but using the Team Date in the X-Axis. Could that be an issue ? I did try using the Team Date in the Slicer however then I did not have the option of using "between" in the Slicer.

Re: Weekly data and slicer

Anonymous — Thu, 13 Apr 2023 15:00:20 GMT

BratKat, changing your analysis to the week of the year would give you better results as week of month analysis is fundamentally flawed for comparable analysis.

If you are slicing by month & week, you will get strange and incomparable results in your visualizations. I would suggest not going about it this way. For example, if the first or last week of the month had anything other than 7 days in them (which they always do), then they would not be comparable to anything and would give misleading results.

Re: Weekly data and slicer

Anonymous — Fri, 14 Apr 2023 01:45:57 GMT

Hi BratKat

Modify the Tooltip measure.

Tooltips = COUNTROWS(FILTER(ALLSELECTED('Table'),[Team Date 01]<=SELECTEDVALUE('Table'[Team Date 01])))

Then put the measure to the tooltip, then select sort by the measure.

Best Regards!

Yolo Zhu

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

Re: Weekly data and slicer

BratKat — Mon, 17 Apr 2023 14:51:33 GMT

MonthWeek column does not appear in Transform Data, unfortunately cannot share my pbix file as it is proprietary as it is FED data. Is there a way to rewrite my current column

MonthWeek = 'Work Items'[Team Date 01].[Month] & " Week " & WEEKNUM('Work Items'[Team Date 01])

per Noah's suggestion ? Thank you