Updating automatic inventory change measure to work for change in Fiscal Year

[Buzz1126]

The below formula calculates the diffeference in entered inventory in the current week from the previous week.  For example, if inventory was 1,000 this week and 250 last week it would output 750.  This works perfectly in all weeks except for when we changed fiscal years from 2019 to 2020 on July 1. 

I need ammend this formula so that specefic week when it went from fiscal week 52 of 2019 to fiscal week 1 of 2020.  Currently, this formula just calculates the change from 0 in week 1 of the fiscal year.  For example, if 1,000 was entered for week 1 of 2020 and 250 was entered for week 52 of 2019, the change would show 1,000 instead of the correct 750.

- The FiscalDayofWeek is just calculating the inventory entered on Friday (or day 6).

- The inventory type and Main Products are the type of inventory.

Last Week Inventory Auto = CALCULATE(
    SUM(FactInventory[Inventory_Volume]),
    FILTER(ALL(DimDate), 
        DimDate[FiscalWeekOfYear] = SELECTEDVALUE(DimDate[FiscalWeekOfYear]) - 1 && //calendarweekofyear or FiscalWeekOfYear
        DimDate[FiscalYear] = SELECTEDVALUE(DimDate[FiscalYear]) &&
        DimDate[FiscalDayOfWeek] = 6),
        
    FILTER(
    	ALL(FactInventory),
    	FactInventory[Type] = "Daily" &&  
    	FactInventory[MainProductName]="Repairable 48x40")
)

Thanks in advance guys!

[Buzz1126]

Is there a way to use a nested If Statement if the week is the first fiscal week of the year?

Something like this?

Last Week Inventory Auto = 
CALCULATE(
    SUM(FactInventory[Inventory_Volume]),
    FILTER(ALL(DimDate),
    IF([FiscalWeekNameFormatted = "07-06-19",//if the week is 07-06-19 (the first week of the fiscal year)
    // code to subtract invenetory in 06-30-19 from 07-06-19
    ,
    //else statement
    
        DimDate[FiscalWefekOfYear] = SELECTEDVALUE(DimDate[FiscalWeekOfYear]) - 1 && //calendarweekofyear or FiscalWeekOfYear
        DimDate[FiscalYear] = SELECTEDVALUE(DimDate[FiscalYear]) &&
        DimDate[FiscalDayOfWeek] = 6),
        
    FILTER(
    	ALL(FactInventory),
    	FactInventory[Type] = "Daily" &&  
    	FactInventory[MainProductName]="Repairable 48x40")
))

[v-juanli-msft · Accepted solution]

Hi @Buzz1126 

First i create Fiscal Year, month, week in calendar table

Create columns

fiscal year = IF(MONTH([Date])<=6,YEAR([Date]),YEAR([Date])+1)

calendar week = WEEKNUM([Date],1)

Financial month number = IF((MONTH([Date])-6)<=0,MONTH([Date])+6,MONTH([Date])-6)

week1 =
VAR firatday =
    DATE ( YEAR ( [Date] ), 7, 1 )
VAR firstdayweek =
    WEEKNUM ( firatday, 1 )
RETURN
    IF (
        MONTH ( [Date] ) > 6,
        IF ( firstdayweek = 26, [calendar week] - 26 + 1, [calendar week] - 26 )
    )

Financial week =
VAR max_week =
    CALCULATE (
        MAX ( [week1] ),
        ALLEXCEPT ( 'fiscal calendar table', 'fiscal calendar table'[fiscal year] )
    )
RETURN
    IF ( MONTH ( [Date] ) <= 6, max_week + [calendar week] - 1, [week1] )

Then create measures in sales table(Sheet3)

this weeek = SUM(Sheet3[value])

previous week =
CALCULATE (
    [this weeek],
    FILTER (
        ALLSELECTED ( 'fiscal calendar table' ),
        'fiscal calendar table'[Financial week]
            = MAX ( 'fiscal calendar table'[Financial week] ) - 1
    )
)


fiscal week1 =
VAR maxfiscalweek =
    CALCULATE (
        MAX ( 'fiscal calendar table'[Financial week] ),
        ALLEXCEPT ( 'fiscal calendar table', 'fiscal calendar table'[fiscal year] )
    )
RETURN
    IF (
        MAX ( 'fiscal calendar table'[Financial week] ) = 1,
        CALCULATE (
            SUM ( Sheet3[value] ),
            FILTER (
                ALLSELECTED ( 'fiscal calendar table' ),
                'fiscal calendar table'[fiscal year]
                    = MAX ( 'fiscal calendar table'[fiscal year] ) - 1
                    && 'fiscal calendar table'[Financial week] = maxfiscalweek
            )
        ),
        [previous week]
    )

diff = [this weeek]-[fiscal week1]

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.