T-14 day slicer

[holodan95]

Hello All,

I have a table, where power bi shows me data for a specific date, selected in a slicer. On the same visualization page I want a similar table, to show the same data, however for a previous date (not intervall)

The selected date is "T-day". What I need, is a "T-14 day" table, so I can check what happened in the previous 14 days.

T-14 should be automatical, so I don't have to select T-day and T-14 day as well on 2 different slicers.

I've already made a working measure, which gives me back the T-14 day date value, however I cannot put a measure into a slicer.

Thank you in advance!

[holodan95 · Accepted solution]

Hello All,

I've finally made it happen. To show the T-14 days, I had to make a simple measure which removes the original date selection and add a new one:

CALCULATE([A measure of your choice], REMOVEFILTERS(XXX[Datum]), DATEADD(XXX[Datum], -14 , DAY))

[holodan95]

Hi,

I've done everything accordingly, but still not working. The tabe only gives back the values for the selected date on the slicer.

But now I'm not sure that I'm doing it right, as there are 3 different dates in the new Measure you've sent:

XXX[Date] (this is the original table with the values), Slicer table [Date] (I assume this is the new table which only has dates in it) Table[Date] which I'm not sure about.

[johnt75]

Table[Date] should be either the date column from your date table or XXX[Date], I think either should work

[holodan95]

Nah, it doesn't work either way. It's possible that the build of the table is the problem.
In the first column there are the main categories, like cookies, spices etc.. In the second column, there are the number of distinct type of products, within the categories (this value is always the same, no matter the date). In the next column, we find the number of disctinct products within the main categories in stock, at a shop.

Now, these values should change, as the slicer date is set (they work). I need a duplicate of the same table but with the values 14 day prior. Please see below:

Let's assume, that these are the values on T day:

Category	Must have	In stock
Cookies	50	32
Spices	45	40

Values on T-14 days:

Category	Must have	In stock
Cookies	50	12
Spices	45	20

Is my table has something to do with the measure you've sent?

Also, the stock 'In stock' is a measure

[johnt75]

Create a measure like

Within 14 days =
VAR CurrentDate =
    SELECTEDVALUE ( 'Date'[Date] )
VAR StartDate = [T-14 date]
VAR EndDate = [T date]
RETURN
    IF ( CurrentDate IN DATESBETWEEN ( 'Date'[Date], StartDate, EndDate ), 1 )

and use that as a filter on your table visual, to only show when the value is 1

[holodan95]

Hello,

I've tried it and it gives back the data for the same day as selected on the original slicer. In my case, "CurrentDate" and "EndDate" is all the same, because on the slicer visual, I manually select the T day, which should be the current date as well.

This is how the final code looks like, adapting yours:

T_14_3 =

VAR CurrentDate  =   SELECTEDVALUE ( XXX[Date] )

VAR StartDate    =   FORMAT(SELECTEDVALUE(XXX[Date], "" ) -14 , "YYYY-MM-DD")

VAR EndDate      =   SELECTEDVALUE(XXX[Date])

RETURN

    IF ( CurrentDate IN DATESBETWEEN ( XXX[Date], StartDate, EndDate ), 1 )

Any idea what I've missed?

[johnt75]

FORMAT would return a string, so I'm not sure the comparison would work. Try

VAR StartDate = CurrentDate - 14

[holodan95]

Hi, It still doesn't work with the changes above 😞

[johnt75]

I think you need a disconnected table. Make a new table which is just a copy of the date column and use that on the slicer. Change the measure to be

Within 14 days =
VAR CurrentDate =
    SELECTEDVALUE ( XXX[Date] )
VAR EndDate =
    SELECTEDVALUE ( 'Slicer table'[Date] )
VAR StartDate = EndDate - 14
RETURN
    IF ( CurrentDate IN DATESBETWEEN ( 'Table'[Date], StartDate, EndDate ), 1 )