Forum Discussion

yoyfreddy's avatar
yoyfreddy
Frequent Visitor
4 years ago
Solved

Date filter not working

Hello experts!

 

I have a table containing the Unit Cost of different Cost Category. I want users to customize the period on time slicer, then display the Total Cost (Unit Cost x No.of Units) across the period automatically. 

 

But, no matter which time period I select, the Total Cost are always the same. Do you know what's wrong with it ?

Here is my measure:

Unit Cost in period = Cost[Unit Cost]* CALCULATE(sum(OpStat[Value]),
DATESBETWEEN('Date'[Date].[Date],FIRSTDATE('Date'[Date]),LASTDATE('Date'[Date])))

 

My BI Dashboard and Data File can be retrieved here :
test - Copy

 

Thanks a lot !

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi  yoyfreddy ,

    You should use measure instead because slicer chart is in report level while calculate column is in data model level so slicer will not affect calculate column. However, slicer can affect the result of a measure.

    Here are the steps you can follow：

    1. Create measure.

    Unit Cost in period_measure =
 MAX( Cost[Unit Cost])*
    CALCULATE(sum(OpStat[Value]),
        DATESBETWEEN('Date'[Date].[Date],FIRSTDATE('Date'[Date]),LASTDATE('Date'[Date])))

    2. Result:

    2021.1.1 – 2021.12.31

    2021.1.1 – 2021.7.14

     

    Best Regards,

    Liu Yang

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

4 Replies

  • StefanoGrimaldi's avatar
    StefanoGrimaldi
    Resident Rockstar
    4 years ago

    hello, 

    1) check if its a date table if the relantionship between tables its set  up correctly

    2) the dax formula its closing the date range directly, delete the last part of the dax and try again: 

    Unit Cost in period = Cost[Unit Cost]* CALCULATE(sum(OpStat[Value])
    there where 2 error here, 1 it a redundancy the condition you had there, cause its making the value to stay in a same range possible instead of taking the filter function 
    • yoyfreddy's avatar
      yoyfreddy
      Frequent Visitor
      4 years ago

      Hi StefanoGrimaldi ,
      Thanks for the quick reply, I tried to use 
      Unit Cost in period = Cost[Unit Cost]* CALCULATE(sum(OpStat[Value])
      but that is not working too. 

      So I suspect it's my date table not working. I saw from other forums some date table have a "people icon" but mine is a "calender" icon. Does it make a difference?

                   

       

      Here is the relationship of my date table, I guess it's a proper settup?

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi  yoyfreddy ,

    You should use measure instead because slicer chart is in report level while calculate column is in data model level so slicer will not affect calculate column. However, slicer can affect the result of a measure.

    Here are the steps you can follow：

    1. Create measure.

    Unit Cost in period_measure =
 MAX( Cost[Unit Cost])*
    CALCULATE(sum(OpStat[Value]),
        DATESBETWEEN('Date'[Date].[Date],FIRSTDATE('Date'[Date]),LASTDATE('Date'[Date])))

    2. Result:

    2021.1.1 – 2021.12.31

    2021.1.1 – 2021.7.14

     

    Best Regards,

    Liu Yang

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

    • yoyfreddy's avatar
      yoyfreddy
      Frequent Visitor
      4 years ago

      Thanks Liu Yang for your help!
      I never think of using MAX function to transform the column into a mearure before reading your comment, so this is a good inspiration too!