cancel
Showing results for
Did you mean:
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

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

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
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)

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

Proud to be a Datanaut !

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

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

Proud to be a Datanaut !

5 REPLIES 5
Member

## Re: Filling balance for missing days

create Measure of Quantity

Measure_Count = COUNT([Quantity])+0

and use this measure in matrix

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

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

Proud to be a Datanaut !

Frequent Visitor

## Re: Filling balance for missing days

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?

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

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

Proud to be a Datanaut !

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

`Test267Out[Date]<EARLIER(Test267Out[Date])`