Forum Discussion
How to filter same column twice on the same chart
I have a table with a Date and a Value columns, I want to create a clusterd barchart that comopares the values of the last 14 days to the set of 14 days before, for example I want to create a clustered barchart that compares the values in the range:
(Jan-30 to Jan-14) with the values (Jan-1 to Jan-13).
The most important think also is I want the whole process relative, so I don't specify start/end date.
Thanks in advance.
Hi Anonymous
1.Create a date table
calendar = CALENDARAUTO()
Don't create any relationship for this table
Add [Date] from this table to a slicer
2. Create measures
max_date = MAX('calendar'[Date]) min_date = MIN('calendar'[Date]) period value = CALCULATE ( SUM ( Sheet8[value] ), FILTER ( ALL ( Sheet8 ), Sheet8[date] <= [max_date] && MAX ( Sheet8[date] ) >= [min_date] ) ) last14_values = CALCULATE ( SUM ( Sheet8[value] ), DATESINPERIOD ( Sheet8[date], [min_date] - 1, -13, DAY ) )Best Regards
MaggieCommunity Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Might be too messy, but...
You could create 2 new columns called "Days Count" or similar. This should be a rather simple formula depending on how many date values you have. Add those two as slicers.
Hopefully they don't try to override each other. I know you can't do this with one one column and 2 slicers. So maybe this'll work.
I think figuring out your start date is very important, even if it's dynamic. PowerBI needs to have some start point, even if it's just using the current date or start date of your data.
You should be able to set this up with the DATESINPERIOD function described here: https://docs.microsoft.com/en-us/dax/datesinperiod-function-dax
Hi Anonymous
1.Create a date table
calendar = CALENDARAUTO()
Don't create any relationship for this table
Add [Date] from this table to a slicer
2. Create measures
max_date = MAX('calendar'[Date]) min_date = MIN('calendar'[Date]) period value = CALCULATE ( SUM ( Sheet8[value] ), FILTER ( ALL ( Sheet8 ), Sheet8[date] <= [max_date] && MAX ( Sheet8[date] ) >= [min_date] ) ) last14_values = CALCULATE ( SUM ( Sheet8[value] ), DATESINPERIOD ( Sheet8[date], [min_date] - 1, -13, DAY ) )Best Regards
MaggieCommunity Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
