Forum Discussion

joshua1990's avatar
joshua1990
Post Prodigy
4 years ago
Solved

Sales YTD based on Week Selection

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.

 

Need Help

12 Replies

  • daXtreme's avatar
    daXtreme
    Solution Sage
    4 years ago

     

    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's avatar
      joshua1990
      Post Prodigy
      4 years ago

      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's avatar
        daXtreme
        Solution Sage
        4 years ago

        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.