datefilterpowerbidaxdatefilter

PowerBI - filter for selected date - 4 weeks

I've got a problem I've had for a while now and can't crack, so seeking some advice.

I have a table with a column of dates with the week beginning date. On my report page I have a slicer that displays the date of this column, and filters various visuals on the page.

In another table, I have a column with the week number, and at the moment I have a graph that displays data for 4 filtered 'Week numbers' - this is the last 4 weeks from today's date. To achieve this, I've created a new calculated column with the following code, and filtered the graph to 'yes':

last4weeks = IF(
    AND(
        DATEDIFF([Week Beginning],TODAY(),WEEK) > 0,
        DATEDIFF([Week Beginning],TODAY(),WEEK) <= 4
    ),
    "Yes",
    "No"
)

What I would like to do, is to apply a similar filter, but one that takes the selected value from the slicer I have on the page (the 'Week Beginning' date) and filter the last 4 'Week Numbers' from that selected date. There is already a relationship between the two tables with a Date column.

I have tried:

last4weeks = 
IF(
    AND(
        DATEDIFF('Firstbeat Data Sheet'[Week Beginning], SELECTEDVALUE('Firstbeat Data Sheet'[Week Beginning]), WEEK) > 0,
        DATEDIFF('Firstbeat Data Sheet'[Week Beginning], SELECTEDVALUE('Firstbeat Data Sheet'[Week Beginning]), WEEK) <= 4
    ),
    "Yes",
    "No"
)

The desired outcome is to have a filter that will say "Yes" if the dates fall within 4 weeks prior to the date on the page slicer, and "No" if not.

Any help much appreciated (example below)

PBI Example

Solution

  • Assuming you don't have a relationship between Table1 and Table2, then you could try:

    last4weeks = 
  var fDate = SELECTEDVALUE('Table1'[Week Beginning], MAX('Table1'[Week Beginning]))
  var wkDiff = DATEDIFF(MIN('Table2'[Week Beginning]), fDate, WEEK)
  return IF(1 <= wkDiff && wkDiff <= 4, "Yes", "No")

    And if you do have a relationship then:

    last4weeks = 
  var fDate = SELECTEDVALUE('Table1'[Week Beginning], MAX('Table1'[Week Beginning]))
  var wkDiff =
    CALCULATE(
      DATEDIFF(MIN('Table2'[Week Beginning]), fDate, WEEK),
      ALL('Table1')
    )
  return IF(1 <= wkDiff && wkDiff <= 4, "Yes", "No")

    Sample PBIX for demo: https://1drv.ms/u/s!AtpcKzRsDrY1o7YwCp1EMbgH6H5RqA?e=VjPXqv