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

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

        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.