Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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:
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:
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.
Table[Date] should be either the date column from your date table or XXX[Date], I think either should work
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
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
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:
FORMAT would return a string, so I'm not sure the comparison would work. Try
VAR StartDate = CurrentDate - 14
Hi, It still doesn't work with the changes above 😞
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 )
User | Count |
---|---|
103 | |
87 | |
78 | |
71 | |
69 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |