How to filter same column twice on the same chart

[Anonymous]

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.

[v-juanli-msft · Accepted solution]

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
Maggie

Community 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.

[Cmcmahan]

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

[Anonymous]

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.

[Anonymous]

That won't solve the issue, I am trying to slice my data into 0-14 and 14-31 days and show these two sets on the same barchart.