Forum Discussion

RichFlorida's avatar
RichFlorida
Icon for Helper V rankHelper V
2 years ago

Filtering on a date range based on slicer value

Hello All, In my report, i'm using a Matrix Visual to display the total of weekly events.      I have a slicer for users to select the startDate of the week. I use a measure to calculate th...
Need Help
vicky_'s avatar
vicky_
Icon for Super User rankSuper User
2 years ago

I would update my calendar table to have more than 1 day included in the "week of" part:

The below would give me my base calendar table:

Calendar Table = ADDCOLUMNS(CALENDAR(DATE(2020, 1, 1), DATE(YEAR(TODAY()), 12, 31)),     
    "WeekNum",  INT(
        SWITCH(TRUE(),
            MONTH([Date]) = 1 && WEEKNUM ([Date] + 1, 21) > 50, YEAR([Date]) - 1, 
            MONTH([Date]) = 12 && WEEKNUM ([Date] + 1, 21) = 1, YEAR([Date]) + 1,
            YEAR([Date])
        ) & FORMAT(WEEKNUM ([Date]  + 1, 21), "00"))
)

The weeknum is taken from another community thread: https://community.fabric.microsoft.com/t5/Desktop/How-to-get-the-ISO-year-in-DAX-while-ISO-week-looks-so-easy-this/td-p/1870758, and it's there to make sure that i have 7 days per week (even if it crosses over a year).

On the above table, I create a calculated column:

Week Of = FORMAT(CALCULATE(MIN('Date'[Date]), ALLEXCEPT('Date', 'Date'[WeekNum])), "dd/mm/yy")

And then to update your measure, you can remove the calculations for the week range:

Num of Events = COUNTA( SearchTable[EVENT_NUMBER])

 

RichFlorida's avatar
RichFlorida
Icon for Helper V rankHelper V
2 years ago

Hi vicky_ 

 

I want the user to select the date the week starts from the slicer, I want my measure to calculate totals for the whole week. 

Currently it is only calculating the total for the specific date selected at the slicer and not for the date range in the measure.

 

Any help greatly apreciated.