Dynamic Calculations and Filtering

[Anonymous]

Good afternoon everyone, 

I would like to be able to add "Current Date Range" slicer and a "Previous Date Range" slicer to the same report, yet I want one slicer to filter one column of data (i.e. Current Period) and the second slicer to filter the other column (i.e. Previous Period). THen I can add measures to calculate the variance and percent variance between the two columns.

I have the following 3 table relationships setup: Table 1 = Dataset (DJ PBIX Files), Table 2 = Current Date (Calendar Table), Table 3 = Previous Date (Copy Calendar Table).

I have added the following measures:

Sales PY =

CALCULATE (

[Sales Total],

ALL ('Current Date'),

USERELATIONSHIP ( 'Current Date'[Date], 'Previous Date'[Date] )

)

However, when I add the slicer filters to the report, one from Current Date Table and one from the Previous Date table, both slicers only filter the current sales total column. They do not filter the column showing the Sales PY data.

Is it possible to have this ability in Power BI?

[V-lianl-msft · Accepted solution]

Hi @Anonymous ,

There is no need to create a relationship between the calendar table and the fact table.

Create a measure like this:

Measure =
CALCULATE (
    SUM ( financials[ Sales] ),
    FILTER (
        financials,
        financials[Date] IN VALUES ( 'Table'[Date] )
            || financials[Date] IN VALUES ( 'Table 2'[Date] )
    )
)

Sample .pbix

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , Refer to if my blog can help. You need two date tables for that. And one of them need to be independent

https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601