Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Changing a date formula with a visual filter

Hi, 

 

I want to change this formular by a visual filter: 

 

Difference Days to Overdue = IF([Progress] = "Finished", BLANK(),DATEDIFF([Due Date (DD/MM/YYYY)], MIN('Calendar'[Date]) ,DAY))
 
The one marked in red should be a selectable value from the created table Calendar. I want a visual filter where you can select a certain range and then the minimum of this range is selected.
 
When I currently insert a visual filter of Calendar[Date] into my report page, the formula does not filter with.
 
Thanks in Advantage.
power bi
time
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous ,

    What you are trying to create is a calculated column? If what you created is a calculated column, it will not change according to the user interaction(slicer, filter, column selections etc.) in the report as the value of a calculated column is computed during data refresh and uses the current row as a context... Please review the following links about the difference of calculated column and measure...

    Calculated Columns and Measures in DAX

    Calculated Columns vs Measures

     

    I created a sample pbix file(see the attachment), please check if that is what you want.

    1. Create a slicer using [Date] field of 'Calendar' table

    2. Create a measure as below to get the dynamic Difference Days by the filter/slicer... 

    Difference Days to Overdue = 
IF (
    SELECTEDVALUE ( 'Table'[Progress] ) = "Finished",
    BLANK (),
    DATEDIFF (
        SELECTEDVALUE ('Table'[Due Date (DD/MM/YYYY)] ),
        MIN ( 'Calendar'[Date] ),
        DAY
    )
)

    Best Regards

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous ,

    What you are trying to create is a calculated column? If what you created is a calculated column, it will not change according to the user interaction(slicer, filter, column selections etc.) in the report as the value of a calculated column is computed during data refresh and uses the current row as a context... Please review the following links about the difference of calculated column and measure...

    Calculated Columns and Measures in DAX

    Calculated Columns vs Measures

     

    I created a sample pbix file(see the attachment), please check if that is what you want.

    1. Create a slicer using [Date] field of 'Calendar' table

    2. Create a measure as below to get the dynamic Difference Days by the filter/slicer... 

    Difference Days to Overdue = 
IF (
    SELECTEDVALUE ( 'Table'[Progress] ) = "Finished",
    BLANK (),
    DATEDIFF (
        SELECTEDVALUE ('Table'[Due Date (DD/MM/YYYY)] ),
        MIN ( 'Calendar'[Date] ),
        DAY
    )
)

    Best Regards

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    Anonymous , Then you have create a measure

     

    example

     

    Difference Days to Overdue = Sumx(Table, IF([Progress] = "Finished", BLANK(),DATEDIFF([Due Date (DD/MM/YYYY)], MIN('Calendar'[Date]) ,DAY)) )