Dynamic Calculation based on FY Date

[Anonymous]

Hi,

I have this table and want to compute the following:

1. Sum of SIO based on  FY YTD = Jul 2022 up to Nov 2022 (Month Year)

Month Year is dynamic. Since today is December, latest Month Year is November. If we move to next month FY YTD is July 2022 to Dec 2022 so the formula should be able to adjust based on that.

2. Sum of SIO based on Previous FY = Jul 2021 to Jun 2022. 

The formula should only sum from Jul 2021 to Jun 2022. This will move to Jul 2022 to Jun 2023 if month of today date is Jul 2023. 

I already have time intelligence in my data fields

[v-henryk-mstf · Accepted solution]

Hi @Anonymous ,

To compute the sum of SIO based on FY YTD and Previous FY in PowerBI, you can use the SUMX and CALCULATE functions.

1 To compute the sum of SIO based on FY YTD, you can use the following formula:

FY YTD =
SUMX (
    FILTER (
        table,
        table[Month Year] >= DATE ( YEAR ( TODAY () ), 7, 1 )
            && table[Month Year] <= MAX ( table[Month Year] )
    ),
    table[SIO]
)

This formula uses the SUMX function to sum the values in the SIO column for all rows in the table where the Month Year column is greater than or equal to July 1 of the current year and less than or equal to the maximum value in the Month Year column.

2 To compute the sum of SIO based on the previous FY, you can use the following formula:

Previous FY =
CALCULATE (
    SUM ( table[SIO] ),
    DATESBETWEEN (
        table[Month Year],
        DATE ( YEAR ( TODAY () ) - 1, 7, 1 ),
        DATE ( YEAR ( TODAY () ), 6, 30 )
    )
)

This formula uses the CALCULATE function to sum the values in the SIO column for all rows in the table where the Month Year column is between July 1 of the previous year and June 30 of the current year.

You can then add these calculated columns to your PowerBI report to display the FY YTD and Previous FY sums.

If the problem is still not resolved, please provide detailed error information and test data. Looking forward to your reply.

Best Regards,
Henry

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