Calculate Fiscal Average with conditions

[hprose]

Hi, I have data in the below format(first 2 columns). The fiscal year end month is June. I want to calculate the fiscal average for FY21 & FY22 as shown in the third column. Can someone help? I tried using TOTALYTD and AVERAGE but it doesnt seem to be working. Besides, I need to apply 2 filters and TOTALYTD doesn't allow it. For example, Column A = "X" && Column B = "Y" in the calculation. Thanks! 

Note that I do have a calendar table.

Also - if the fiscal year hasn't ended, it needs to be YTD average. My goal is to add an average line for each fiscal year along with the actual values.

MonthYear	Value	Fiscal Average
Jul-20	200	750
Aug-20	300	750
Sep-20	400	750
Oct-20	500	750
Nov-20	600	750
Dec-20	700	750
Jan-21	800	750
Feb-21	900	750
Mar-21	1000	750
Apr-21	1100	750
May-21	1200	750
Jun-21	1300	750
Jul-21	1400	1950
Aug-21	1500	1950
Sep-21	1600	1950
Oct-21	1700	1950
Nov-21	1800	1950
Dec-21	1900	1950
Jan-22	2000	1950
Feb-22	2100	1950
Mar-22	2200	1950
Apr-22	2300	1950
May-22	2400	1950
Jun-22	2500	1950
July-22	2600	2800
Aug-22	3000	2800

[hprose · Accepted solution]

I did end up solving this. It's not the most elegant solution,  but it gets me the result I want. Hope this helps someone. 

Formulas: 

2022Rolling = 
CALCULATE([Value$],DATESYTD(Calendar[Calendar_Date],"06/30"),Calendar[Fiscal_Year]=2022)

--My calendar only has months, that's why I'm using countrows here.
Months2022 = 
IF(SELECTEDVALUE(CALENDAR[Fiscal Year]) = 2021, Blank(),CALCULATE(COUNROWS(Calendar),Calendar[Fiscal Year] = 2022,ALL(Calendar)))

2022YTD_constant = 
IF(SELECTEDVALUE(CALENDAR[Fiscal Year]) = 2021, Blank(),CALCULATE([2022Rolling],ALL(Calendar)))

FiscalAverage2022 = CALCULATE(2022YTD_constant/Months2022)

Do the same for another fiscal year if needed. This will give you dynamic average lines similar to the chart below.

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[hprose]

Hi @v-rzhou-msft ,

Thank you very much for the detailed response. Apologies, I should have provided some use cases for more context. I'm looking to calculate dynamic averages based on filters selected, so unfortunately, the calculated column did not work. However, I did end up solving this. Thanks for your help!

[hprose]

@Ashish_Mathur Thank you for response. I'm looking to have the average be visible alongside all the months. I should have included more use cases. I did end up solving this. Appreciate your help!

[v-rzhou-msft]

Hi @hprose ,

Here I suggest you to create a Calendar table with Fiscal Year and then create calculated columns.

Calendar = 
VAR _BASIC =
    ADDCOLUMNS (
        CALENDAR ( DATE ( 2020, 01, 01 ), DATE ( 2022, 12, 31 ) ),
        "Year", YEAR ( [Date] ),
        "Month", MONTH ( [Date] ),
        "MonthName", FORMAT ( [Date], "MMMM" ),
        "MonthYearNum",
            YEAR ( [Date] ) * 100
                + MONTH ( [Date] ),
        "MonthYear", FORMAT ( [Date], "MMM-YY" )
    )
VAR _FISCAL =
    ADDCOLUMNS ( _BASIC, "FiscalYear", IF ( [Month] > 6, [Year] + 1, [Year] ) )
RETURN
    _FISCAL

Calculated columns:

Fiscal Year = 
CALCULATE(MAX('Calendar'[FiscalYear]))

Fiscal Average = AVERAGEX(FILTER('Table',[Fiscal Year] = EARLIER('Table'[Fiscal Year])),[Value])

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.