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 o...
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
Mahesh0016's avatar
Mahesh0016
Icon for Super User rankSuper 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 !