Forum Discussion

joshua1990's avatar
joshua1990
Icon for Post Prodigy rankPost 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 w...
Need Help
daXtreme's avatar
daXtreme
Icon for Solution Sage rankSolution 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.

joshua1990's avatar
joshua1990
Icon for Post Prodigy rankPost Prodigy
4 years ago

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

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

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

        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.