Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Help with Quarter data display

Hi All,

 

I am struggling with a requirement to display quarterly data.

I created a disconnected table to filter quarters as below with index column using 

DAX:  Index = RANKX('Parameter Table','Parameter Table'[Year Quarter],,ASC,Dense)

 Tried below dax to filter and get last 3 quarters including current quarter data which is working fine.

Flag =
VAR _max =
SELECTEDVALUE ( 'Parameter Table'[Year Quarter] )
VAR _min =
CALCULATE (
MAX ('Parameter Table'[Year Quarter]),
FILTER (
ALL ( 'Parameter Table'),
'Parameter Table'[Index]
= SELECTEDVALUE ( 'Parameter Table'[Index] ) - 3
)
)
VAR _yearQuarter =
(MAX ( Master[Year] ))* 10
+ MAX ( Master[Quarter_Num] )
RETURN
IF ( _yearQuarter <= _max && _yearQuarter >= _min, 1, 0 )

 

But my requirement is as below:

 

When I select 2021Q4 it should display previous all years Q4 data including 2021Q4.

Ex: 

2021Q4

2020Q4

2019Q4

2018Q4

 

When I select 2021Q3 it should display 2021Q3 and previous years Q4 data. same logic for Q2 or Q1 as well.

Ex:

2021Q3

2020Q4

2019Q4

2018Q4

 

Sample date:

 

YearQuarterCountryValue
2018Q4America10
2018Q4Australia15
2018Q4UK20
2019Q1America25
2019Q2Australia30
2019Q3UK35
2019Q4America40
2019Q1America45
2019Q2Australia50
2019Q3America55
2019Q4UK60
2020Q1America65
2020Q2America70
2020Q3Australia75
2020Q4America80
2021Q1UK85
2021Q2America90
2021Q3America95
2021Q4Australia100
2022Q1UK105
2022Q1America110

 

Can someone please suggest.

Thank you

 

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

     

    Please firstly create a new table to get all  distinct Years and Quarters for slicer:

    For Slicer = DISTINCT( SELECTCOLUMNS('Table',"Year",[Year],"Quarter",[Quarter]) )

     

    Then create a flag measure :

    Flag =
VAR _seleYear =
    SELECTEDVALUE ( 'For Slicer'[Year] )
VAR _seleQuar =
    SELECTEDVALUE ( 'For Slicer'[Quarter] )
RETURN
    IF (
        _seleQuar = "Q4",
        IF (
            MAX ( 'Table'[Year] ) <= _seleYear
                && MAX ( 'Table'[Quarter] ) = _seleQuar,
            1,
            0
        ),
        IF (
            (
                MAX ( 'Table'[Year] ) = _seleYear
                    && MAX ( 'Table'[Quarter] ) = _seleQuar
            )
                || (
                    MAX ( 'Table'[Year] ) < _seleYear
                        && MAX ( 'Table'[Quarter] ) = "Q4"
                ),
            1,
            0
        )
    )

    Then apply it to visual-filter pane, set as "is 1":

     

     

     


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

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

     

    Please firstly create a new table to get all  distinct Years and Quarters for slicer:

    For Slicer = DISTINCT( SELECTCOLUMNS('Table',"Year",[Year],"Quarter",[Quarter]) )

     

    Then create a flag measure :

    Flag =
VAR _seleYear =
    SELECTEDVALUE ( 'For Slicer'[Year] )
VAR _seleQuar =
    SELECTEDVALUE ( 'For Slicer'[Quarter] )
RETURN
    IF (
        _seleQuar = "Q4",
        IF (
            MAX ( 'Table'[Year] ) <= _seleYear
                && MAX ( 'Table'[Quarter] ) = _seleQuar,
            1,
            0
        ),
        IF (
            (
                MAX ( 'Table'[Year] ) = _seleYear
                    && MAX ( 'Table'[Quarter] ) = _seleQuar
            )
                || (
                    MAX ( 'Table'[Year] ) < _seleYear
                        && MAX ( 'Table'[Quarter] ) = "Q4"
                ),
            1,
            0
        )
    )

    Then apply it to visual-filter pane, set as "is 1":

     

     

     


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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Anonymous ,

      Thank you so much for the help this is what I expected.

      Quick question can we do a percentage difference for the same.

      like Quarter over Quarter

       

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , if you select a value in slicer and then you need more that that, you need an independent table for the slicer

     

    have a table with year , qtr and Year Qtr etc. Use a slicer on that

    measure =

    var _tab =summarize(allselected(Date), Date[Qtr])

    return

    calculate(sum(Table[Value]), filter(Table, Table[Qtr] in _tab))

     

    Need of an Independent Table in Power BI: https://youtu.be/lOEW-YUrAbE

     

    Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Amit,

       

      I am new to power bi tried multiple approaches but still struggling to achieve what I want.

      I created a new date table as mentioned in your video still no luck.

      Could you please guide me with the calculation

      when I select any quarter in the selection we need to display previous all years Q4 data including the selected quarter.