cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nsbars Frequent Visitor
Frequent Visitor

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

s1.jpg

 

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.

s2.jpg

 

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

 

Thanks in advance.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User
Super User

Re: Filling balance for missing days

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

 

image.png

 



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





Super User
Super User

Re: Filling balance for missing days

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

image.pngimage.png

 



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





5 REPLIES 5

Re: Filling balance for missing days

create Measure of Quantity

 

Measure_Count = COUNT([Quantity])+0

 

and use this measure in matrix

Highlighted
Super User
Super User

Re: Filling balance for missing days

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

 

image.png

 



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





nsbars Frequent Visitor
Frequent Visitor

Re: Filling balance for missing days

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?

s4.jpg

 

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

s3.jpg

Super User
Super User

Re: Filling balance for missing days

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

image.pngimage.png

 



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





nsbars Frequent Visitor
Frequent Visitor

Re: Filling balance for missing days

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!