topic Re: Calculate Closing Balance for Week in Every Month in DAX Commands and Tips

Calculate Closing Balance for Week in Every Month

avinash2807 — Tue, 09 Aug 2022 11:49:53 GMT

I want to calculate closing balance of week ,

I have two tables one sales table with sales on each day and another calendar table with each day , week number, and week range

Sales Table

CalendarDate QtyAvailable Category

2022-07-01 30 Category1

2022-07-01 15 Category2

2022-07-02 28 Category1

2022-07-02 15 Category2

2022-07-03 12 Category2

2022-07-03 26 Category2

2022-07-04 32 Category2

2022-07-04 22 Category2

2022-07-05 36 Category2

2022-07-05 16 Category2

2022-07-06 23 Category2

2022-07-06 33 Category2

2022-07-07 24 Category2

2022-07-08 13 Category2

2022-07-09 44 Category2

2022-07-09 24 Category2

CalendarTable WeekNumber WeekRange

2022-07-01 27 June26-Jul2

2022-07-02 27 June26-Jul2

2022-07-03 28 July-3-July9

2022-07-04 28 July-3-July9

2022-07-05 28 July-3-July9

2022-07-06 28 July-3-July9

2022-07-07 28 July-3-July9

2022-07-08 28 July-3-July9

2022-07-09 28 July-3-July9

I want to display Qty available on Last Day of Each Week

WeekRange QtyAvailable

June26-Jul2 43 --- Sum of Qty Available on July 2 which is weeks last day across all catgories (28 for Cat1 and 15 Cat2 , Sum=43)

July-3-July9 68---Sum of Qty Available on July 9 which is weeks last day across all catgories ( 44 for Cat1 and 24 Cat2 , Sum=68)

Re: Calculate Closing Balance for Week in Every Month

Jihwan_Kim — Tue, 09 Aug 2022 14:51:04 GMT

Hi,

Please check the below picture and the attached pbix file.

lastnonblankdate = LASTNONBLANK ( Sales[CalendarDate], CALCULATE ( SUM ( Sales[QtyAvailable] ) ) )

lastnonblankvaue by category: = VAR _lastnonblankdatebycategory = ADDCOLUMNS ( SUMMARIZE ( Sales, Category[Category] ), "@lastnonblankdate", [lastnonblankdate] ) RETURN IF ( HASONEVALUE ( CalendarTable[WeekRange] ), CALCULATE ( SUM ( Sales[QtyAvailable] ), TREATAS ( _lastnonblankdatebycategory, Category[Category], CalendarTable[CalendarDate] ) ) )

Re: Calculate Closing Balance for Week in Every Month

avinash2807 — Tue, 09 Aug 2022 17:34:40 GMT

works very well, thanks Jihwan_Kim for the quick help

Re: Calculate Closing Balance for Week in Every Month

og_gremlins — Mon, 11 Dec 2023 14:52:53 GMT

Hello Jihwan_Kim ,
I wanted to use your method but I encounter a problem with it and I can't find a way to make it works. The issue is that how do I handle cases where a category has not been updated in a week range. I need to still use the last QuantityAvailable. For exemple with these data :

If my week range is Jan08-Jan14, final balance should be : 95808,99 + (-1646984,4056) + 91288,25 = -1459887,17

However with the current formula it ignores the value that is not in WeekRange. How could we tackle this issue. The aim is then to display this measure in matrix with WeekRange as column. What are your thoughts or ideas about this ?