Forum Discussion

avinash2807's avatar
avinash2807
Regular Visitor
4 years ago
Solved

Calculate Closing Balance for Week in Every Month

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  Cale...
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

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

     

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
4 years ago

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's avatar
og_gremlins
Frequent Visitor
2 years ago

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 ?