Cleanest way to do YoY calculations for financial year starting in July

[htipene]

Hi,

I live in Australia so our financial year starts in July. I've found it surprisingly difficult to get YoY and YTD calculations to work using standard formulas in Power BI. I'm also not having much luck searching these forums. I have colleagues who have created workarounds but they seem pretty messy and complicated, so I was hoping to refer here for the cleanest possible way to do these calculations.

So making this as simple as possible, let's say I have a sales table aggregated by week, and a date table. Below is some dummy data for the first three weeks of financial year 2020 and 2021, and also the week preceding those dates. Note the need to use a custom date table that supplies what the equivalent week is from last year. This is because some financial years have 53 weeks and so you need to keep track of that historically, I have noticed this can mess up calculations using the standard formulas in Power BI. See tables below:

This year week	Sales
202152	400
202201	600
202202	500
202203	700
202052	300
202101	500
202102	400
202103	600

Financial week	Previous year financial week	Date
202152	202052	June 24 2021
202201	202101	July 1 2021
202202	202102	July 8 2021
202203	202103	July 15 2021
202052	201952	June 25 2020
202101	202001	July 2 2020
202102	202002	July 9 2020
202103	202003	July 16 2020

So what I need is as follows:

• Calculated field that returns sales an growth for the past 52 weeks and YTD for financial year starting in July
• Calculated field that returns sales and growth for previous 52 weeks and previous YTD for financial year starting in July
• Ability to enter these fields into a KPI visual so it shows this year, last year and growth
• Ability to enter these fields into a line graph so it shows this year and last year sales using the same axis, with this year's date on the X axis

So the above example should return the following:

• YoY sales this year: 2200, YTD sales this year: 1800
• YoY sales last year: 1800, YTD sales last year: 1500
• YoY growth: 22%, YTD growth: 20%

[v-zhangti · Accepted solution]

Hi, @htipene 

You can try the following methods.

Sales =
LOOKUPVALUE ( Sales[Sales], 'Financial week'[Financial week], [Financial week] )

YoY sales = 
CALCULATE (
    SUM ( 'Financial week'[Sales] ),
    FILTER (
        'Financial week',
        [Date].[Year] = EARLIER ( 'Financial week'[Date].[Year] )
    )
)

YTD sales = 
CALCULATE (
    SUM ( 'Financial week'[Sales] ),
    FILTER (
        'Financial week',
        [Date].[MonthNo] = 7
            && [Date].[Year] = EARLIER ( 'Financial week'[Date].[Year] )
    )
)

YoY growth =
DIVIDE (
    MAX ( 'Financial week'[YoY sales] ) - MIN ( 'Financial week'[YoY sales] ),
    CALCULATE (
        MAX ( 'Financial week'[YoY sales] ),
        FILTER ( ALL ( 'Financial week' ), [Date].[Year] = 2020 )
    )
)

YTD growth = 
DIVIDE (
    MAX ( 'Financial week'[YTD sales] ) - MIN ( 'Financial week'[YTD sales] ),
    CALCULATE (
        MAX ( 'Financial week'[YTD sales] ),
        FILTER ( ALL ( 'Financial week' ), [Date].[Year] = 2020 )
    )
)

If the method I provided above can't solve your problem, what's your expected result? Could you please provide more details for it?

Best Regards,

Community Support Team _Charlotte

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

[MFelix]

Hi @htipene ,

On the time inteliggence calculations there is an optional parameter that is the fiscal month, so you can select where the year starts and end:

DATESYTD(<dates> [,<year_end_date>])

Term	Definition
dates	A column that contains dates.
year_end_date	(optional) A literal string with a date that defines the year-end date. The default is December 31.

TOTALYTD(<expression>,<dates>[,<filter>][,<year_end_date>])

PARAMETERS

Parameter	Definition
expression	An expression that returns a scalar value.
dates	A column that contains dates.
filter	(optional) An expression that specifies a filter to apply to the current context.
year_end_date	(optional) A literal string with a date that defines the year-end date. The default is December 31.

Taking into account you have Date and a Date table you can sinply add that parameter value to your calculation

Check documentatio below for both of the DAX above.

https://dax.guide/datesytd/

https://dax.guide/totalytd/