Calculate last FY figures with multiple years

[SoniaRadaelli]

I'm using the following measure to calculate the growth of this fiscal year period (e.g. april to sept)  Vs LY same period but if LFY is 0 the result is not correct as the formula apply the growth respect to another FY

example:

FY2020     sales 100

FY2019     sales  0

FY2018    sales 90

DAX result +11% (100/90-1)

Here the measure I'm using:

% Vs. LY orig =

VAR first_date =

CALCULATE ( (MIN( sales_totale[anno FY] )), ALL ( sales_totale[anno FY] ) )

VAR date_selection =

CALCULATE (

MAX ( sales_totale[anno FY] ),

FILTER ( ALL ( sales_totale[anno FY] ), ( sales_totale[anno FY] ) < MAX ( sales_totale[anno FY] ) ))

RETURN

IF ( MIN ( sales_totale[anno FY] ) = first_date,

BLANK (),

SUM ( sales_totale[UBLNAM] ) /

CALCULATE ( SUM ( sales_totale[UBLNAM] ),

FILTER ( ALL ( sales_totale[anno FY] ), sales_totale[anno FY] = date_selection ) )-1 )

Any Idea?

Thanks

[MFelix]

Hi @SoniaRadaelli ,

Try the following measure:

% Vs. LY orig = 

VAR first_date =
CALCULATE ( (MIN( sales_totale[anno FY] )), ALL ( sales_totale[anno FY] ) )
VAR lastdatenonblank =
    FILTER (
        SUMMARIZE (
            ALL ( sales_totale[anno FY], sales_totale[Sales] ),
            sales_totale[anno FY],
            "@sales", SUM ( sales_totale[Sales] )
        ),
        [@sales] > 0
            && sales_totale[anno FY] < MAX ( sales_totale[anno FY] )
    )
RETURN
    IF(MAX(sales_totale[anno FY]) = first_date,
DIVIDE (
        SUM ( sales_totale[Sales] ),
        CALCULATE (
            SUM ( sales_totale[Sales] ),
            FILTER (
                ALL ( sales_totale[anno FY] ),
                sales_totale[anno FY] = MAXX ( lastdatenonblank, sales_totale[anno FY] )
            )
        )
    ) - 1)

In your case since you were making only the selewction of the values below the max year selection the value you returned was 2019 value and not 2018.

[SoniaRadaelli]

Hi @MFelix 

here the result using your dax

🤔

modifying your if statement, adding the underlined part:

IF(MAX(sales_totale[anno FY]) = first_date,

BLANK(),

DIVIDE (

SUM ( sales_totale[UBLNAM] ),

CALCULATE (

SUM ( sales_totale[UBLNAM] ),

FILTER (

ALL ( sales_totale[anno FY] ),

sales_totale[anno FY] = MAXX ( lastdatenonblank, sales_totale[anno FY] )

)

)

) - 1)

I have the following result

as you can see in FY2020 the % is calculated respect to FY2018 and not on FY2019

[MFelix · Accepted solution]

Hi @SoniaRadaelli ,,

Try the following measure below:

% Vs. LYS = 
VAR first_date =
    CALCULATE ( ( MIN ( SALESSHARED[anno FYS] ) ), ALL ( SALESSHARED[anno FYS] ) )
VAR lastdatenonblank =
FILTER(ALL(SALESSHARED[anno FYS]), SALESSHARED[anno FYS] < MAX(SALESSHARED[anno FYS]))
RETURN
    IF (
        MAX ( SALESSHARED[anno FYS] ) = first_date,
        BLANK (),
        DIVIDE (
            SUM ( SALESSHARED[NETTO] ),
            CALCULATE (
                SUM ( SALESSHARED[NETTO] ),
                FILTER (
                    ALL ( SALESSHARED[anno FYS] ),
                    SALESSHARED[anno FYS] = MAXX ( lastdatenonblank, SALESSHARED[anno FYS] )
                )
            )
        ) - 1
    )

[MFelix]

Hi @SoniaRadaelli 

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.