Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Dynamic quarter chart without slicer

Hi all,

 

I want to show sales for rolling last 8 quarters. My FY cycle is June-May.

 

For example if I take March '22, the bar chart should show previous 8 quarters of data, i.e

2022 - Q1, Q2, Q3.

2021 - Q1, Q2, Q3, Q4.

2020 - Q4

 

Uptil the end of this quarter which would be in May '22, the above mentioned quarters should only be shown. As soon as I move to Q1 '23, the chart should show bars of 2022 - Q1 to Q4 and 2021 - Q1 to Q4 so on and so fourth. Also this chart should not be governed by any date slicer, say year or month, it should be a standalone chart.

 

Any help on how can I able to achieve this would be appreciated.

 

Thanks in advance!

Need Help
Show and Tell
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    Hi Anonymous ,

     

    I create the following Dates table and relationship.

     

       

     

    Then please try the measure:

     

    Measure = 
VAR CurrentDate =
    TODAY ()
VAR CurYear_Qua =
    CALCULATE (
        MAX ( Dates[FY_Year] ) & MAX ( Dates[FY_Quarter] ),
        Dates[Date] = CurrentDate
    )
VAR Qua_FirstDay =
    CALCULATE (
        MIN ( Dates[Date] ),
        FILTER ( ALL ( Dates ), Dates[FY_Year] & Dates[FY_Quarter] = CurYear_Qua )
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            Dates,
            Dates[Date] >= EDATE ( Qua_FirstDay, -24 )
                && Dates[Date] < Qua_FirstDay
        )
    )


    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

3 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      I already have a date table in place, which takes min and max dates values from the data table.

  • v-kkf-msft's avatar
    v-kkf-msft
    Community Support
    4 years ago

    Hi Anonymous ,

     

    I create the following Dates table and relationship.

     

       

     

    Then please try the measure:

     

    Measure = 
VAR CurrentDate =
    TODAY ()
VAR CurYear_Qua =
    CALCULATE (
        MAX ( Dates[FY_Year] ) & MAX ( Dates[FY_Quarter] ),
        Dates[Date] = CurrentDate
    )
VAR Qua_FirstDay =
    CALCULATE (
        MIN ( Dates[Date] ),
        FILTER ( ALL ( Dates ), Dates[FY_Year] & Dates[FY_Quarter] = CurYear_Qua )
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            Dates,
            Dates[Date] >= EDATE ( Qua_FirstDay, -24 )
                && Dates[Date] < Qua_FirstDay
        )
    )


    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.