Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Dynamic Calculations and Filtering

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?
Need Help
  • V-lianl-msft's avatar
    V-lianl-msft
    5 years ago

    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.

2 Replies

  • V-lianl-msft's avatar
    V-lianl-msft
    Community Support
    5 years ago

    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.