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 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.

Need Help
Tips and Tricks
  • PattemManohar's avatar
    PattemManohar
    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)

     

5 Replies

  • PattemManohar's avatar
    PattemManohar
    Icon for Community Champion rankCommunity Champion
    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)

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        PattemManohar
        Icon for Community Champion rankCommunity Champion
        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)

         

  • adityavighne's avatar
    adityavighne
    Icon for Continued Contributor rankContinued Contributor
    7 years ago

    create Measure of Quantity

     

    Measure_Count = COUNT([Quantity])+0

     

    and use this measure in matrix