Forum Discussion

PatrickMcLeanGL's avatar
PatrickMcLeanGL
Frequent Visitor
1 year ago
Solved

Applying Different Date Filters to Different Columns in a Table Visual

I want to convert this table I set up in Excel to Power Bi. It looks at performance values for various tasks on the date chosen (in this image, March 25th) as well as quantity values, and then compares them to a standard and also takes the average over the last 30 days (for performance) and sum of last 30 days (for quantity).

When I try to set it up in Power Bi, I can create a new measure for the last 30 days value, but if I apply a date slicer it will affect all columns even the last 30 days column where I specify a separate date filter. I can leave a date slicer/picker out, and it will be accurate and show the data for Today's date as well as the average over the last 30 days, but as soon as I want to go back and look at data from two days ago, the date slicer will override my last 30 days value.

 

Without date filter (shows todays values, and then average of last 30 days)

Adding a date slicer (sets last 30 to same value as perf):

 

 

 

Need Help
  • SVADDE21's avatar
    SVADDE21
    1 year ago

    Hi PatrickMcLeanGL ,

     

    You can use below query to ignore the filters applied to Date slicer in last30days measure. The below is just a reference to show Removefilters expression.

    Last30DaysValue = 
CALCULATE(
    SUM(Table[Value]),
    DATESBETWEEN(
        Table[Date],
        TODAY() - 30,
        TODAY()
    ),
    REMOVEFILTERS(DateTable[Date])
)

     

2 Replies

  • SVADDE21's avatar
    SVADDE21
    Frequent Visitor
    1 year ago

    Hi PatrickMcLeanGL ,

     

    You can use below query to ignore the filters applied to Date slicer in last30days measure. The below is just a reference to show Removefilters expression.

    Last30DaysValue = 
CALCULATE(
    SUM(Table[Value]),
    DATESBETWEEN(
        Table[Date],
        TODAY() - 30,
        TODAY()
    ),
    REMOVEFILTERS(DateTable[Date])
)

     

  • v-venuppu's avatar
    v-venuppu
    Icon for Community Support rankCommunity Support
    1 year ago

    Hi

    May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

    Thank you.

    Regards,

    Rama U.