Forum Discussion

anusha_2023's avatar
anusha_2023
Icon for Helper IV rankHelper IV
1 year ago
Solved

Using Year-Month Slicer Calculating the Avearge 3 month values of Quarterly values

I am trying to calculate the Average values of each Quarter and following rolling 8 Quarters. If 2024-11 is selected then 8 quarters need to show from 2023-Q1 to 2024-Q4 as below. Now need to ...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi anusha_2023 ,

     

    Please try code as below to update your measure.

    Measure = 
VAR Reference_Date = MAX('Date'[Date])

VAR NumberofMonths =
SWITCH(
    TRUE(),
    MONTH(Reference_Date) IN {1, 4, 7, 10}, 22,
    MONTH(Reference_Date) IN {2, 5, 8, 11}, 23,
    MONTH(Reference_Date) IN {3, 6, 9, 12}, 24,
    BLANK() -- Default case
)

VAR RollingMonths = 
    DATESINPERIOD(
        'Recurring date'[Date],
        Reference_Date,
        -NumberofMonths,
        MONTH
    )

VAR _R1 = 
    CALCULATE(
        SUM(BalanceSheetSample[Amount]), 
        REMOVEFILTERS('Date'),
        KEEPFILTERS(RollingMonths),
        USERELATIONSHIP('Date'[Date], 'Recurring date'[Date])
    )

VAR RollingMonths2 = 
    DATESINPERIOD(
        'Recurring date'[Date],
        Reference_Date,
        -3,
        MONTH
    )
VAR _R2 = 
    CALCULATE(
        SUM(BalanceSheetSample[Amount]), 
        REMOVEFILTERS('Date'),
        KEEPFILTERS(RollingMonths2),
        USERELATIONSHIP('Date'[Date], 'Recurring date'[Date]),
        ALLSELECTED('Recurring Date')
    )
RETURN
IF(MAX('Recurring Date'[YearQuarter]) = FORMAT(Reference_Date,"YYYY")&"/Q"&FORMAT(Reference_Date,"Q"),_R2,_R1)

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

    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
1 year ago

Hi anusha_2023 ,

 

I think your issue is based on your data model. I suggest you to add an unrelated Year Quarter table for X axis in your chart. 

Please share a sample file with us and show us a screenshot with the result you want. This will make it easier to find the solution.

 

Best Regards,
Rico Zhou

 

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

anusha_2023's avatar
anusha_2023
Icon for Helper IV rankHelper IV
1 year ago

Thanks for the reply.

I made a sample visual as below. Of 2023-03 selected from slicer then in the Quarter visual 2023-Q1 has 2023-01, 2023-02, and 2023-03 months that is normal.

Now I need to modify to achive the speacial cases if 2023-04 is selected from the slicer then 2023-Q2 Contains 2023-02, 2023-04,2023-05. I have shared the sample from the google drive. Please suggest me further steps  https://drive.google.com/file/d/10UUwpXEz_SaHsOwvVvVe91E-2cn1S_6J/view?usp=drive_link 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi anusha_2023 ,

     

    It seems that we don't have permission to your data source link.

    Please approve our request or you can share a new link with public permission to us.

     

    Best Regards,
    Rico Zhou

     

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

    • anusha_2023's avatar
      anusha_2023
      Icon for Helper IV rankHelper IV
      1 year ago

      I have granted the permission to access the file

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi anusha_2023 ,

         

        Please try code as below to update your measure.

        Measure = 
VAR Reference_Date = MAX('Date'[Date])

VAR NumberofMonths =
SWITCH(
    TRUE(),
    MONTH(Reference_Date) IN {1, 4, 7, 10}, 22,
    MONTH(Reference_Date) IN {2, 5, 8, 11}, 23,
    MONTH(Reference_Date) IN {3, 6, 9, 12}, 24,
    BLANK() -- Default case
)

VAR RollingMonths = 
    DATESINPERIOD(
        'Recurring date'[Date],
        Reference_Date,
        -NumberofMonths,
        MONTH
    )

VAR _R1 = 
    CALCULATE(
        SUM(BalanceSheetSample[Amount]), 
        REMOVEFILTERS('Date'),
        KEEPFILTERS(RollingMonths),
        USERELATIONSHIP('Date'[Date], 'Recurring date'[Date])
    )

VAR RollingMonths2 = 
    DATESINPERIOD(
        'Recurring date'[Date],
        Reference_Date,
        -3,
        MONTH
    )
VAR _R2 = 
    CALCULATE(
        SUM(BalanceSheetSample[Amount]), 
        REMOVEFILTERS('Date'),
        KEEPFILTERS(RollingMonths2),
        USERELATIONSHIP('Date'[Date], 'Recurring date'[Date]),
        ALLSELECTED('Recurring Date')
    )
RETURN
IF(MAX('Recurring Date'[YearQuarter]) = FORMAT(Reference_Date,"YYYY")&"/Q"&FORMAT(Reference_Date,"Q"),_R2,_R1)

        Result is as below.

         

        Best Regards,
        Rico Zhou

         

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