Forum Discussion

mim's avatar
mim
Advocate V
6 years ago
Solved

group by dynamic week

I have this calendar that calculated week based on a particular weekend, which is the last date from another table using this measure "Cutt off daily", everything was fine, untill someone wanted a sl...
Need Help
  • Brian_M's avatar
    Brian_M
    6 years ago

    Note that the Calendar table has to be marked as a date table for the time intelligence (DATESBETWEEN) functions to work.

     

    Based on your response on twitter, the pbix file also has a YTD version for the sum of qty.

     

    Hope that helps!

Brian_M's avatar
Brian_M
Continued Contributor
6 years ago

I undesrtand you wish to group all of the sales for each given week into a seven day period ending on the same day of the week as the selected slicer.

 

The measure below checks to see if our date on the X-axis is the same day of the week as your slicer. If not it returns BLANK( ), if it is the same day of week, it sums the qty for the previous 7 days.

 

Qty at 'week end' =
VAR SelectedSlicerDate =
    SELECTEDVALUE ( 'Slicer Dates'[Date] )
VAR ThisDateOnAxis =
    SELECTEDVALUE ( 'Calendar'[Date] )
RETURN
    IF (
        WEEKDAY ( SelectedSlicerDate ) <> WEEKDAY ( ThisDateOnAxis ),
        BLANK (),
        CALCULATE (
            SUM ( 'Data Table'[qty] ),
            DATESBETWEEN ( 'Calendar'[Date], ThisDateOnAxis - 6, ThisDateOnAxis )
        )
    )

 

I'll attempt to link to a copy of the pbix

Example PBIX file 

  • Brian_M's avatar
    Brian_M
    Continued Contributor
    6 years ago

    Note that the Calendar table has to be marked as a date table for the time intelligence (DATESBETWEEN) functions to work.

     

    Based on your response on twitter, the pbix file also has a YTD version for the sum of qty.

     

    Hope that helps!