Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Relative date Filter - In the next 2 months but exclude the next month

Dear community,

 

I have 3 visuals where each of the visuals should show only the specific values for "this month", "next month", "2+ month".

Example for this month:
Visual 1: Shows only values for October 2021 ("this month")

Visual 2: Shows only values for November 2021 ("next month")

Visual 3: Shows only values for December 2021 ("2+ month")

 

I have managed the first two visuals with the relative date filter. But I have a problem with the third visual, because if I make it with the relative filter, it includes the values for November AND December.

 

My question is, how i have to change the filters that Visual 3 only shows the values for the month which is in the next 2 months.

Need Help
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    Hi Anonymous 

     

    The visual you used is card or chart?

    if card , copy these measures  and use it directly

    This month =
VAR _max =
    EOMONTH( SELECTEDVALUE( 'Kapa'[Datum] ), 0 ) //  SELECTEDVALUE('Kapa'[Datum])  <==>  TODAY() if you want to calculate daily
VAR _min =
    DATE( YEAR( _max ), MONTH( _max ), 1 )
RETURN
    CALCULATE(
        SUM( Kapa[Auslastung] ),
        FILTER( ALL( Kapa ), [Datum] >= _min && [Datum] <= _max )
    )

    Next month =
VAR _s =
    SELECTEDVALUE( Kapa[Datum] )
VAR _max =
    DATE( YEAR( _s ), MONTH( _s ) + 2, 1 ) - 1
VAR _min =
    DATE( YEAR( _s ), MONTH( _s ) + 1, 1 )
RETURN
    CALCULATE(
        SUM( Kapa[Auslastung] ),
        FILTER( ALL( Kapa ), [Datum] >= _min && [Datum] <= _max )
    )

    2+ month =
VAR _s =
    SELECTEDVALUE( Kapa[Datum] )
VAR _max =
    DATE( YEAR( _s ), MONTH( _s ) + 3, 1 ) - 1
VAR _min =
    DATE( YEAR( _s ), MONTH( _s ) + 2, 1 )
RETURN
    CALCULATE(
        SUM( Kapa[Auslastung] ),
        FILTER( ALL( Kapa ), [Datum] >= _min && [Datum] <= _max )
    )

     

    if you use chart maybe some difference. please provide some example data and expect visual.i put pbix file in the attachment you can refer.

     

    Best Regards

    Community Support Team _ chenwu zhu

     

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

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Anonymous , Try a measure like

     

    Value =
    var _max = eomonth(Minxmaxx(allselected('Date'), 'Date'[Date]),4)
    var _min = eomonth(Minx(allselected('Date'), 'Date'[Date]),1)+1
    return
    calculate(sum(Table[Value]), filter(Table, Table[Date]>= _min && Table[Date] <=_max))

     

    or

     

    Value =
    var _max = eomonth(Minxmaxx(allselected('Date'), 'Date'[Date]),4)
    var _min = eomonth(Minx(allselected('Date'), 'Date'[Date]),1)+1
    return
    calculate(sum(Table[Value]), filter(all(Date), Date[Date]>= _min && Date[Date] <=_max))

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      amitchandak thanks for your response, but I need some further help.

      My data looks like this:
      Data

       

      I just need the field "Datum", which contains the date and the sum of the measure "Auslastung". How can I fill the data with your proposed measure, because I coudn't manage to make it work.

      • v-chenwuz-msft's avatar
        v-chenwuz-msft
        Icon for Community Support rankCommunity Support
        4 years ago

        Hi Anonymous 

         

        The visual you used is card or chart?

        if card , copy these measures  and use it directly

        This month =
VAR _max =
    EOMONTH( SELECTEDVALUE( 'Kapa'[Datum] ), 0 ) //  SELECTEDVALUE('Kapa'[Datum])  <==>  TODAY() if you want to calculate daily
VAR _min =
    DATE( YEAR( _max ), MONTH( _max ), 1 )
RETURN
    CALCULATE(
        SUM( Kapa[Auslastung] ),
        FILTER( ALL( Kapa ), [Datum] >= _min && [Datum] <= _max )
    )

        Next month =
VAR _s =
    SELECTEDVALUE( Kapa[Datum] )
VAR _max =
    DATE( YEAR( _s ), MONTH( _s ) + 2, 1 ) - 1
VAR _min =
    DATE( YEAR( _s ), MONTH( _s ) + 1, 1 )
RETURN
    CALCULATE(
        SUM( Kapa[Auslastung] ),
        FILTER( ALL( Kapa ), [Datum] >= _min && [Datum] <= _max )
    )

        2+ month =
VAR _s =
    SELECTEDVALUE( Kapa[Datum] )
VAR _max =
    DATE( YEAR( _s ), MONTH( _s ) + 3, 1 ) - 1
VAR _min =
    DATE( YEAR( _s ), MONTH( _s ) + 2, 1 )
RETURN
    CALCULATE(
        SUM( Kapa[Auslastung] ),
        FILTER( ALL( Kapa ), [Datum] >= _min && [Datum] <= _max )
    )

         

        if you use chart maybe some difference. please provide some example data and expect visual.i put pbix file in the attachment you can refer.

         

        Best Regards

        Community Support Team _ chenwu zhu

         

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