Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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.
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") ))
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |