Power BI Visual use one side of date slicer

Question

I have a visual that needs to show trending 6 weeks of a measure, however, the other visuals on the report are based on a custom range. So when looking at the report, there is a date slicer that has a start date and an end date.

What I need to do is create a measure/visual that shows a 6-week trend based off of the last date selected in that slicer. So even if you select 2 weeks in the date filter, the visual will show the last 6 weeks.

The end user selects a date range (anywhere from 1-2 weeks), and I need to show a 6 week trending chart based on the last day in the range selected. So in order to do this, I need to find a way to see the max date selected in the slicer to use and have the visual independent from said slicer. In other words, if you select last Monday thru last Friday, the visual will show a 6 week trend ending last Friday.

So I know that I can use the DAX function LASTDATE() to get the last date that is in the slicer, then subtract 6 weeks from that. The issue that I am running into is that when I create the visual, both dates on the slicer force it to the date range selected (not 6 weeks). When I change the visual interactions for the visual, then the last date in the visual is no longer the date selected.

Any ideas on how to get this to work?

Edit: Here is the code that I am working with:

Two tables

Sales

Sales ID | Customer ID | Qty  | Sales Date
   1            A         2   | 12/2/2018
   2            A         11  | 12/2/2018
   3            B         5   | 11/28/2018
   4            B         7   | 11/28/2018
   5            C         10  | 11/27/2018
   6            D         20  | 11/27/2018
   7            E         9   | 11/25/2018

Calendar

    Date    |  Weeknum
12/2/2018         49
12/1/2018         48
11/30/2018        48
11/29/2018        48
11/28/2018        48
11/27/2018        48
11/26/2018        48
11/25/2018        47
11/24/2018        47
...And so on

The DAX statement that I am using to get the starting date (6 weeks from the max date selected):

Six Weeks From Last Date Selected =
DATEADD(LASTDATE('Calendar'[Date]), -42, DAY) 

Six Weeks Sales:

6 Weeks Sales =
CALCULATE(
    SUM('Sales'[Sales]),
    DATESBETWEEN(
        'Calendar'[Date],
        [Six Weeks From Last Date Selected],
        LASTDATE('Calendar'[Date])
    )
)

Thanks for any help in advance!

Answer 1

I'm guessing that your measure is taking the sum on the intersection of the selected date range and the six weeks range.

If you want to always get the six weeks, then you should remove the filter context on the date column by adding ALL('Calendar'[Date]) to your CALCULATE function:

6 Weeks Sales =
CALCULATE(
    SUM('Sales'[Sales]),
    ALL('Calendar'[Date]),
    DATESBETWEEN(
        'Calendar'[Date],
        [Six Weeks From Last Date Selected],
        LASTDATE('Calendar'[Date])
    )
)

---

Edit: The above will give you the 6 weeks sales value, but not what you're after.

To do what you want, you'll need to create a second calendar table related to Sales.

Cal2 = 'Calendar'

Use Cal2[Date] for your visual instead of 'Calendar'[Date] and write a measure that removes the 'Calendar' filtering and puts in 6 weeks filtering instead. For example:

6 Weeks Sales = 
VAR EndDate = LASTDATE('Calendar'[Date])
VAR StartDate = EndDate - 42
VAR CurrDate = SELECTEDVALUE(Cal2[Date])
RETURN
IF( StartDate < CurrDate && CurrDate <= EndDate,
    CALCULATE(
        SUM(Sales[Qty]),
        ALL('Calendar')
    ),
    BLANK()
)

You could also do this with a filter instead:

6 Weeks Sales = 
VAR EndDate = LASTDATE('Calendar'[Date])
VAR StartDate = EndDate - 42
RETURN
CALCULATE(
    SUM(Sales[Qty]),
    ALL('Calendar'[Date]),
    FILTER(
        Cal2,
        StartDate < Cal2[Date] && Cal2[Date] <= EndDate
    )
)