Sales YTD based on Week Selection

[joshua1990]

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.

[daXtreme]

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.

[joshua1990]

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.

[daXtreme]

@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.

[joshua1990]

@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. 

[daXtreme]

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.

[joshua1990]

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?

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
    IF(ISBLANK(Result), BLANK(),Result)

[daXtreme · Accepted solution]

Attached is a sketch for the solution...

[joshua1990]

@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.

[daXtreme]

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.

[joshua1990]

@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.

[daXtreme]

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.

[daXtreme]

@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.