Solved: Re: Sum of some xyz column for previous 4 quarter ...

Anonymous · ‎04-04-2022

Hi , I have a somewhat similar problem....i need to calculate a sum of some xyz column for previous 4 quarter end months..

I Have FiscalQuarter FiscalPeriod Year-Month

FiscalQuarter FiscalPeriod Year-Month
4 12 01-12-2024 00:00
4 11 01-11-2024 00:00
4 10 01-10-2024 00:00
3 9 01-09-2024 00:00
3 8 01-08-2024 00:00
3 7 01-07-2024 00:00
2 6 01-06-2024 00:00
2 5 01-05-2024 00:00
2 4 01-04-2024 00:00
1 3 01-03-2024 00:00
1 2 01-02-2024 00:00
1 1 01-01-2024 00:00
4 12 01-12-2023 00:00
4 11 01-11-2023 00:00
4 10 01-10-2023 00:00
3 9 01-09-2023 00:00
3 8 01-08-2023 00:00
3 7 01-07-2023 00:00
2 6 01-06-2023 00:00
2 5 01-05-2023 00:00
2 4 01-04-2023 00:00
1 3 01-03-2023 00:00
1 2 01-02-2023 00:00
1 1 01-01-2023 00:00
4 12 01-12-2022 00:00
4 11 01-11-2022 00:00
4 10 01-10-2022 00:00
3 9 01-09-2022 00:00
3 8 01-08-2022 00:00
3 7 01-07-2022 00:00
2 6 01-06-2022 00:00
2 5 01-05-2022 00:00
2 4 01-04-2022 00:00
1 3 01-03-2022 00:00
1 2 01-02-2022 00:00
1 1 01-01-2022 00:00
4 12 01-12-2021 00:00
4 11 01-11-2021 00:00
4 10 01-10-2021 00:00
3 9 01-09-2021 00:00
3 8 01-08-2021 00:00
3 7 01-07-2021 00:00
2 6 01-06-2021 00:00
2 5 01-05-2021 00:00
2 4 01-04-2021 00:00
1 3 01-03-2021 00:00
1 2 01-02-2021 00:00
1 1 01-01-2021 00:00
4 12 01-12-2020 00:00
4 11 01-11-2020 00:00
4 10 01-10-2020 00:00
3 9 01-09-2020 00:00
3 8 01-08-2020 00:00
3 7 01-07-2020 00:00
2 6 01-06-2020 00:00
2 5 01-05-2020 00:00
2 4 01-04-2020 00:00
1 3 01-03-2020 00:00
1 2 01-02-2020 00:00
1 1 01-01-2020 00:00
4 12 01-12-2019 00:00
4 11 01-11-2019 00:00
4 10 01-10-2019 00:00
3 9 01-09-2019 00:00
3 8 01-08-2019 00:00
3 7 01-07-2019 00:00
2 6 01-06-2019 00:00
2 5 01-05-2019 00:00
2 4 01-04-2019 00:00
1 3 01-03-2019 00:00
1 2 01-02-2019 00:00
1 1 01-01-2019 00:00

so for today the dax should be ..... as we are in 2nd quarter...2nd quarter end month is 6... so we start from 6 and go backwards...

Calculate(sum(xyz),period=6,year=2022)+Calculate(sum(xyz),period=3,year=2022)+Calculate(sum(xyz),period=12,year=2021)+Calculate(sum(xyz),period=9,year=2021)...

when we reach 7th month dax would be
Calculate(sum(xyz),period=9,year=2022)+Calculate(sum(xyz),period=6,year=2022)+Calculate(sum(xyz),period=3,year=2022)+Calculate(sum(xyz),period=12,year=2021)..

how can i automate it so i dont have to change values after every quarter...

v-janeyg-msft · ‎04-07-2022

Hi, @Anonymous

According to your description, I create a sample file.

You need to create a calculate column and a measure to diaplay the dynamic total value.

Like this:

column:

quarterendmonth = if(MONTH('Table'[Date]) in {3,6,9,12},YEAR([Date])*100+MONTH([Date]))

meaure:

Total = 
VAR a =
    CALCULATE (
        MIN ( 'Table'[quarterendmonth] ),
        MONTH ( 'Table'[Date] ) >= MONTH ( TODAY () )
            && YEAR ( 'Table'[Date] ) = YEAR ( TODAY () )
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALL ( 'Table' ),
            [quarterendmonth] >= a - 97
                && [quarterendmonth] <= a
        )
    )

Did I answer your question? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,
Community Support Team _ Janey

View solution in original post

v-janeyg-msft · ‎04-07-2022