Forum Discussion
Anonymous
7 years agoNot applicable
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...
- 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)
- 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)
PattemManohar
Community Champion
7 years agoAnonymous 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)
Anonymous
7 years agoNot applicable
Hello PattemManohar
Thank you, that's exactly what I needed!
Whith little change
Test267Out[Date]<=EARLIER(Test267Out[Date])
instead of
Test267Out[Date]<EARLIER(Test267Out[Date])
Many thanks!