Filling balance for missing days

[Anonymous]

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 there're no records in source table for that date.

In my example it should be 30 for SKU1 at 02.03.19 and 20 for SKU2 at 03.03.19

Thanks in advance.

[PattemManohar · Accepted solution]

@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 · Accepted solution]

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

[Anonymous]

Hi  @PattemManohar 

Thank you, I feel that it is aslmost solution.

But which measure or condition should I use to get Quantity at previous non blank date value instead zeroes?

First I wanted to add CALCULATE instead zero, but got blank:

[PattemManohar · Accepted solution]

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

[Anonymous]

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!

[adityavighne]

create Measure of Quantity

Measure_Count = COUNT([Quantity])+0

and use this measure in matrix