Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi Community,
I am working on a sales table (1), when the sales value is null I have to return the previous nonblankvalue for each product (2).
I used the following formula but it's not returning the values I am looking for:
Could you please help me correcting my formula to get the green column.
Thank you in advance.
Solved! Go to Solution.
[Column] = var __product = Products[Product] var __date = __Products[DateKey] var __lookupTable = filter( filter( Products, Products[Product] = __product ), NOT ISBLANK( Products[Sales] ) && Products[DateKey] <= __date ) var __sales = MAXX( TOPN( 1, __lookupTable, Products[DateKey] ), Products[Sales] ) return __sales
Best
Darek
[Column] = var __product = Products[Product] var __date = __Products[DateKey] var __lookupTable = filter( filter( Products, Products[Product] = __product ), NOT ISBLANK( Products[Sales] ) && Products[DateKey] <= __date ) var __sales = MAXX( TOPN( 1, __lookupTable, Products[DateKey] ), Products[Sales] ) return __sales
Best
Darek
Hi @Anonymous ,
Thank you for the solution. It works 🙂
User | Count |
---|---|
51 | |
28 | |
19 | |
18 | |
14 |
User | Count |
---|---|
91 | |
86 | |
39 | |
23 | |
22 |