Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Filling balance for missing days

Hello I'm new in DAX and in this forum, sorry if something wrong. I have such table with balance by SKU   I've created matrix visual and the task is to show last actual balance even if ther...
  • PattemManohar's avatar
    7 years ago

    Anonymous Create a new table as below and use this table for your Matrix visual

     

    Test267Out = UNION(
                        SELECTCOLUMNS(
                                        EXCEPT(
                                                CROSSJOIN(VALUES(Test267MatrixBlankZero[Date]),VALUES(Test267MatrixBlankZero[SKU]))
                                               ,SELECTCOLUMNS(Test267MatrixBlankZero,"Date",[Date],"SKU",[SKU])
                                              )
                                     ,"Date",[Date],"SKU",[SKU],"Qty",0
                                     )
                      ,Test267MatrixBlankZero
                        )

    Here is the screenshot of both actual (using the source table) and expected (using the above new calculated table)

     

     

  • PattemManohar's avatar
    PattemManohar
    7 years ago

    Anonymous  Please add an another column to the new table that was created as above

     

    NewQty = 
    VAR _PrevDate = CALCULATE(MAX([Date]),FILTER(Test267Out,Test267Out[SKU]=EARLIER(Test267Out[SKU]) && Test267Out[Date]<EARLIER(Test267Out[Date]) && Test267Out[Qty] <> 0))
    VAR _Lkp = LOOKUPVALUE(Test267Out[Qty],Test267Out[SKU],Test267Out[SKU],Test267Out[Date],_PrevDate)
    RETURN IF(ISBLANK(_Lkp),Test267Out[Qty],_Lkp)