Forum Discussion

Gattinomio's avatar
Gattinomio
Icon for Helper I rankHelper I
2 years ago

Display data for previous 4 quarters based on selected date in slicer

Hi everybody,   I have been trying to solve this issue on my own for a while and I am stuck. So I would like to ask for your support.   I have a dashboard where the users can select the time fra...
Need Help
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi, Gattinomio 

You can try the following measure. If you want to achieve this effect, the relationship between the Date table and the Measure table cannot be activated, if you want to use this slicer to achieve the effect of filtering other visuals, you can use the Related, userrelationship function to activate the relationship.

previous 4 quarters flag =
VAR _startDate =
    CALCULATE ( MIN ( 'Date'[Date] ) )
VAR _endDate =
    CALCULATE ( MAX ( 'Date'[Date] ) )
VAR _date_diff = _endDate - _startDate
VAR _ture_endDate = _startDate + 365
VAR _re =
    IF (
        _date_diff <= 365,
        IF (
            SELECTEDVALUE ( 'Measure'[Date] ) >= _startDate
                && SELECTEDVALUE ( 'Measure'[Date] ) <= _endDate,
            1,
            0
        ),
        IF ( SELECTEDVALUE ( 'Measure'[Date] ) > _ture_endDate, 0, 1 )
    )
RETURN
    _re

 

Best Regards,
Yang
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

  • Gattinomio's avatar
    Gattinomio
    Icon for Helper I rankHelper I
    2 years ago

    Dear Yang,

    many thanks for having taken the time to work on a solution for me and to have answered.
    I'm currently traveling and I was not able to look at the solution you suggested in details yet. I will do so by the end of the week.
    Many thanks again and have a nice day!

  • Gattinomio's avatar
    Gattinomio
    Icon for Helper I rankHelper I
    2 years ago

    Dear Yang,

     

    many thanks again for your help.

     

    I wasn't able to use the measure exactly as you wrote it but thanks to your suggestion I tweaked it to write two new measures:

    To filter the data for the past 4 quarters

    Filter 4 Last Quarters =
    VAR _startDate =
        CALCULATE ( MIN ( 'Date Slicer'[Date] ) )
    VAR _endDate =
        CALCULATE ( MAX ( 'Date Slicer'[Date] ) )
    VAR _paststartDate = EDATE(_startDate,-12)
    VAR _re =
        IF (
            MIN(dates[date]) >= _paststartDate
            && MAX(dates[date]) <= _endDate,
                1,
                0
            )
    RETURN
        _re
     
    To filter for the selected quarter
    Filter Selected Quarter =
    VAR _startDate =
        CALCULATE ( MIN ( 'Date Slicer'[Date] ) )
    VAR _endDate =
        CALCULATE ( MAX ( 'Date Slicer'[Date] ) )
    VAR _re =
        IF (
            MIN(dates[date]) >= _startDate
            && MAX(dates[date]) <= _endDate,
                1,
                0
            )
    RETURN
        _re
     
    The intent with these measures is to only have one slicer on the dashboard where the user will select the quarter he wants to see and depending on the visual I will use one filter or the other to display either the selected quarter or the selected quarter and the previous 3.
     
    These formulas work perfectly in a matrix visual where the year and quarters are displayed. But I am unable to use them in a card visual. I do not get the option to use the drop down list and to set the filter to 1.
    The result is also not correct when these fields are used in a matrix visual with several categories and aggregated date fields (quarter + year).
     
    Do you know what I might be doing wrong?
     
    Many thanks again for your help.
    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi, Gattinomio 

      Have you currently resolved this problem, if not, you can share the pbix file without sensitive data for testing, or show your current progress as a picture. From your description, it looks like what you want to do is create a quarterly slicer that filters the first four quarters of data based on the quarterly slicer selection.

      Best Regards,
      Yang
      Community Support Team