Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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)
Proud to be a PBI Community Champion
@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)
Proud to be a PBI Community Champion
@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)
Proud to be a PBI Community Champion
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:
@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)
Proud to be a PBI Community Champion
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!
create Measure of Quantity
Measure_Count = COUNT([Quantity])+0
and use this measure in matrix
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
77 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |