topic Re: Sales YTD based on Week Selection in DAX Commands and Tips

Sales YTD based on Week Selection

joshua1990 — Fri, 02 Sep 2022 06:53:30 GMT

Hi all,

I have a week filter on top of the report and then a simple bar chart that shows me the sales per month.

Based on the week selection I would like to display all sales YTD until this week.

This approach is not working:

SalesYTD = CALCULATE ( [Sales], FILTER ( ALL ( 'Calendar' ), 'Calendar'[Year] = MAX ( 'Calendar'[Year] ) && 'Calendar'[Week] <= MAX ( 'Calendar'[Week] ) ) )

This measure shows me the YTD volume based on the current month. The total sum is just displayed in the last month. But I would like to get all months display with the individual sales.

Re: Sales YTD based on Week Selection

daXtreme — Fri, 02 Sep 2022 08:31:33 GMT

Sales YTD = var LastVisibleDate = MAX( 'Calendar'[Date] ) var CurrentYear = MAX( 'Calendar'[Year] ) // Year must be integer var Result = CALCULATE( [Sales], 'Calendar'[Date] <= LastVisibleDate, 'Calendar'[Year] = CurrentYear, REMOVEFILTERS( 'Calendar' ) ) return Result // or... if your 'Calendar' is a proper one: Sales YTD = CALCULATE( [Sales], DATESYTD( 'Calendar'[Date] ) )

If this is not what you want, then please clarify what you mean by:

"This measure shows me the YTD volume based on the current month. The total sum is just displayed in the last month. But I would like to get all months display with the individual sales."

You could do with some picture.

Re: Sales YTD based on Week Selection

joshua1990 — Fri, 02 Sep 2022 10:26:30 GMT

Thanks a lot for your support daXtreme :

This is the picture without a Week Filter / Slicer

And this is the picture when I select Week 35 for instance:

When a week filter is applied, then I would like to get the YTD volume per Month until this MAX week.

Currently it is summed up as you can see in the picture above.

Re: Sales YTD based on Week Selection

daXtreme — Fri, 02 Sep 2022 10:36:46 GMT

joshua1990

To do what you want you cannot put the dates on the x-axis as you do right now because if you filter the calendar (from which you've taken the periods), the x-axis will also get filtered and DAX has nothing to do with this; that's how PBI works. You have to have a time axis that's independent of anything else, in a word: disconnected. Then you drop any of the time periods from the new time dimension on the visual and create a measure that works with just this table. So, if you want to make some pieces of time visible (in your case, from the beginning of the year up to and including the month that your week is part of), the measure should return a number; if you want to hide some parts of the axis, you just return blank. This is how you must set it all up.

Re: Sales YTD based on Week Selection

joshua1990 — Fri, 02 Sep 2022 12:05:26 GMT

daXtreme : Thanks a lot. I understand the concept with the disconnected table, but I don't get the point how to create an appropriate measure.

Re: Sales YTD based on Week Selection

daXtreme — Fri, 02 Sep 2022 18:44:16 GMT

Well, just start working on it and you'll see the point immediately 🙂 Just remember that when a measure returns a BLANK for a dimension attribute's value, the attribute gets hidden from the user's view.

Re: Sales YTD based on Week Selection

joshua1990 — Sat, 03 Sep 2022 09:20:38 GMT

I found a solution but it is not perfect. It will display BLANK(), if the actual measure is blank as well. A better solution would be to limit the second calendar table based on LastVisibleDate but I don't know how. Do you have an idea?

Re: Sales YTD based on Week Selection

daXtreme — Sat, 03 Sep 2022 09:46:44 GMT

joshua1990

This is something you should not write:

IF(ISBLANK(Result), BLANK(),Result)

Have you really read this statement? What does it say? In English: If the Result is blank, return blank; otherwise, return Result. So, it's exactly the same as:

Result

I'll create a simple model to show you how you should deal with the problem at hand. Bear with me, please.

Re: Sales YTD based on Week Selection

daXtreme — Sat, 03 Sep 2022 11:10:26 GMT

Attached is a sketch for the solution...

Re: Sales YTD based on Week Selection

joshua1990 — Sat, 03 Sep 2022 12:13:27 GMT

daXtreme : Thanks a lot, now I got it! Would you take the same approach if the report contains 15 measures to be displayed like this? So 15 different measures displayed in the same way just on multiple pages.

Re: Sales YTD based on Week Selection

daXtreme — Sat, 03 Sep 2022 20:26:03 GMT

Hi there.

Yes, probably. Whether or not you need to do what I've done in the file depends on your needs. If you want to be able to select a piece of time (like a week, say), and show many other pieces of time depending on the selection, then yes, you have to have an independent time axis and measures that will work with it. Of course, you can decrease the number of measures by employing calculation groups but that requires some knowledge of calculation groups (you can learn this on www.sqlbi.com) and using the Tabular Editor. One other technique comes to my mind and it's illustrated here by Alberto Ferrari but it might not be as versatile as the one I've shown you. But it might be what you want, so please check it out.

Re: Sales YTD based on Week Selection

joshua1990 — Mon, 05 Sep 2022 12:12:43 GMT

daXtreme : Thank you so much for your support and detailed explenation. This helps much!

I will take a closer look at calculation groups.

I just tried to leverage your approach for a MTD approach, but it is not running yet. Do you know why?

Sales MTD = var DatesLastDate = MAX( 'Calendar'[Date] ) var DatesVisibleMonth = SELECTEDVALUE( 'Calendar'[MonthOffset], -1) var TimeAxisVisibleMonth = SELECTEDVALUE( 'Calendar 2'[MonthOffset] ) var TimeAxisLastVisibleDate = MAX( 'Calendar 2'[Date] ) var TimeAxisFirstVisibleDate = MIN( 'Calendar 2'[Date] ) var ShouldCalculate = and( DatesVisibleMonth = TimeAxisVisibleMonth, TimeAxisFirstVisibleDate <= DatesLastDate ) var Output = if( ShouldCalculate, CALCULATE( [Sales], 'Calendar'[Date] <= TimeAxisLastVisibleDate ) ) return Output

I just changed the Year to MonthOffset, but I get nothing displayed.

Re: Sales YTD based on Week Selection

daXtreme — Mon, 05 Sep 2022 15:38:18 GMT

No idea. I'd need the data itself to have a look. But I'd encourage you to really, really, really watch this: "here by Alberto Ferrari." You'll find a different technique that may be more relevant and maybe even easier to leverage.