Calculate Closing Balance for Week in Every Month

[avinash2807]

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-07     24                  Category2

2022-07-08     13                  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)

[Jihwan_Kim · Accepted solution]

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]
            )
        )
    )

[og_gremlins]

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 ?

[avinash2807]

works very well, thanks @Jihwan_Kim for the quick help