Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

dynamic filter based on another

Hi,

I would like to compare the number of values in a column in different time ranges.

For example, if a user select with a slicer the time range "last 2 monthes", I would like to show the number of elements in the column the last 2 monthes AND the number of elements in the column the 2 monthes BEFORE 2 monthes ago.

If he select with the slicer the dates 01/24/2022 - 01/31/2022, I would like to have the result between these dates AND between 01/16/2022 - 01/23/2022.

Hope I am clear

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Mahesh0016's avatar
    Mahesh0016
    3 years ago

    ShowDate lastperiod =
    VAR _RangeStart =
        MIN ( 'Date'[Date] )
    VAR _RangeEnd =
        MAX ( 'Date'[Date] )

    VAR DATE_DIFF =
    (DATEDIFF(_RangeStart,_RangeEnd,DAY))+1

    VAR DATE_ADD_MIN = _RangeStart-DATE_DIFF

    VAR DATE_ADD_MAX = _RangeEnd-DATE_DIFF

    VAR RESULT = CALCULATE(SUM(TrainingSample2[UnitPrice]),DATESBETWEEN('Date'[Date],DATE_ADD_MIN,DATE_ADD_MAX))

    RETURN
    RESULT
     
    **********************************************************************
    _ShowDate lastperiod =
    VAR _RangeStart =
        MIN ( 'Date'[Date] )
    VAR _RangeEnd =
        MAX ( 'Date'[Date] )

    VAR DATE_DIFF =
    (DATEDIFF(_RangeStart,_RangeEnd,DAY))+1

    VAR DATE_ADD_MIN = _RangeStart-DATE_DIFF

    VAR DATE_ADD_MAX = _RangeEnd-DATE_DIFF

    VAR RESULT = CALCULATE(SUM(TrainingSample2[UnitPrice]),DATESBETWEEN('Date'[Date],DATE_ADD_MIN,DATE_ADD_MAX))

    RETURN
    DATE_ADD_MIN & " - " & DATE_ADD_MAX

2 Replies

  • Mahesh0016's avatar
    Mahesh0016
    Super User
    3 years ago

    ShowDate lastperiod =
    VAR _RangeStart =
        MIN ( 'Date'[Date] )
    VAR _RangeEnd =
        MAX ( 'Date'[Date] )

    VAR DATE_DIFF =
    (DATEDIFF(_RangeStart,_RangeEnd,DAY))+1

    VAR DATE_ADD_MIN = _RangeStart-DATE_DIFF

    VAR DATE_ADD_MAX = _RangeEnd-DATE_DIFF

    VAR RESULT = CALCULATE(SUM(TrainingSample2[UnitPrice]),DATESBETWEEN('Date'[Date],DATE_ADD_MIN,DATE_ADD_MAX))

    RETURN
    RESULT
     
    **********************************************************************
    _ShowDate lastperiod =
    VAR _RangeStart =
        MIN ( 'Date'[Date] )
    VAR _RangeEnd =
        MAX ( 'Date'[Date] )

    VAR DATE_DIFF =
    (DATEDIFF(_RangeStart,_RangeEnd,DAY))+1

    VAR DATE_ADD_MIN = _RangeStart-DATE_DIFF

    VAR DATE_ADD_MAX = _RangeEnd-DATE_DIFF

    VAR RESULT = CALCULATE(SUM(TrainingSample2[UnitPrice]),DATESBETWEEN('Date'[Date],DATE_ADD_MIN,DATE_ADD_MAX))

    RETURN
    DATE_ADD_MIN & " - " & DATE_ADD_MAX
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thank you it is exactly what I needed !