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 frame at the top with a slicer.

 

 

The visualisations in the upper part of the dashboard reflect the data for the selected time frame (always 3 months).

 

My issue is for the visualisations in the lower part of the dashboard which should reflect data for the selected quarter and the previous 4 as shown below as an example:

 

In my data model I have the following:

- Date Table

- Measure Table (linked to Date Table on date) with measures and categories

- Independant Date Table

 

I did some research on this forum and have already created an additional independant date table for the slicer.

I tried several formulas but none of them worked. I would need something that gives me the previous 4 quarters (or 12 months) from the min date selected in the slicer.

 

Any input would be very appreciated.

Many thanks in advance!

Need Help

5 Replies

  • 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

  • Ajithkumar_03's avatar
    Ajithkumar_03
    Frequent Visitor
    2 years ago

    Hi Gattinomio,

    I need one clarity from your end please confirm. you want last 12 month (4 Qtr) from min selected date or max selected date?

    Regards,
    AjithKumar