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.
Dear Gentlemen
Hi, I have a table that looks like the below table.
ITEM | DATE | CAT | VALUE |
A | 01-Jan-2016 | 8 | 2 |
B | 01-Jan-2016 | 5 | 7 |
C | 01-Jan-2016 | 5 | |
A | 02-Jan-2016 | 10 | |
B | 02-Jan-2016 | 4 | 6 |
C | 02-Jan-2016 | 9 | 1 |
A | 03-Jan-2016 | 7 | 3 |
B | 03-Jan-2016 | 8 | |
C | 03-Jan-2016 | 4 | |
A | 04-Jan-2016 | 6 | |
B | 04-Jan-2016 | 6 | |
C | 04-Jan-2016 | 6 | 2 |
A | 05-Jan-2016 | 8 | 2 |
B | 05-Jan-2016 | 5 | 5 |
C | 05-Jan-2016 | 5 | |
A | 06-Jan-2016 | 10 | |
B | 06-Jan-2016 | 4 | 6 |
C | 06-Jan-2016 | 9 | 1 |
A | 07-Jan-2016 | 7 | 3 |
B | 07-Jan-2016 | 8 | |
C | 07-Jan-2016 | 4 | |
A | 08-Jan-2016 | 6 | |
B | 08-Jan-2016 | 6 | |
C | 08-Jan-2016 | 6 | 1 |
A | 09-Jan-2016 | 8 | 2 |
B | 09-Jan-2016 | 5 | 4 |
C | 09-Jan-2016 | 5 | |
A | 10-Jan-2016 | 10 | |
B | 10-Jan-2016 | 4 | 6 |
C | 10-Jan-2016 | 9 | 1 |
A | 11-Jan-2016 | 7 | 3 |
B | 11-Jan-2016 | 8 | |
C | 11-Jan-2016 | 4 | |
A | 12-Jan-2016 | 6 | |
B | 12-Jan-2016 | 6 | 5 |
C | 12-Jan-2016 | 6 | 2 |
and i'd like to fill the blank value . based on the Items and date ,
i Try 2 solutions but both are fill the gap basd on the latest max value instead of last non blank and here is a secreen shot of the result . below i make filter based on item to check the results.
here is is the first formula i used .
Test1 = IF ( Table1[VAL] = BLANK (), CALCULATE ( LASTNONBLANK ( Table1[VAL], Table1[VAL] ), FILTER ( ALLEXCEPT ( Table1, Table1[ITEM] ), Table1[DATE] <= EARLIER ( Table1[DATE] ) ) ), Table1[VAL] )
and this is the second one
Test2 = IF(ISBLANK(Table1[VAL]), CALCULATE(LASTNONBLANK(Table1[VAL],0), FILTER(Table1, Table1[DATE]<EARLIER(Table1[DATE])&& Table1[ITEM]=EARLIER(Table1[ITEM])&& [DATE]=LASTDATE(Table1[DATE])&& not(ISBLANK(Table1[VAL])) ) ),Table1[VAL])
and i'd like to reach out to the below result . "Exp Result"
Thank you for your Help & support
Solved! Go to Solution.
Try this one
Test1 = VAR MyDate = IF ( Table1[VALUE] = BLANK (), CALCULATE ( MAX ( Table1[DATE] ), FILTER ( ALLEXCEPT ( Table1, Table1[ITEM] ), Table1[DATE] < EARLIER ( Table1[DATE] ) && Table1[VALUE] <> BLANK () ) ) ) RETURN IF ( Table1[VALUE] = BLANK (), CALCULATE ( SUM ( Table1[VALUE] ), FILTER ( ALLEXCEPT ( Table1, Table1[ITEM] ), Table1[DATE] = MyDate ) ), [VALUE] )
This one should also work
Test2 = VAR temp = TOPN ( 1, FILTER ( Table1, Table1[ITEM] = EARLIER ( Table1[ITEM] ) && Table1[DATE] < EARLIER ( Table1[DATE] ) && Table1[VALUE] <> BLANK () ), [DATE], DESC ) RETURN IF ( ISBLANK ( Table1[VALUE] ), MINX ( temp, [VALUE] ), [VALUE] )
Hi @jaafar82.
Have you solved your problem?
If you have solved, please accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.
If you still need help, please feel free to ask.
Best Regards,
Cherry
Yes i got the solution and just accepted as a solution
Thank you very much
Hi @jaafar82,
You could easily use the Fill feature in "Edit Queries" instead of DAX. It will return exactly what you are looking for.
Br,
T
Thank you for your solution "but i need it in DAX" 🙂
Try this one
Test1 = VAR MyDate = IF ( Table1[VALUE] = BLANK (), CALCULATE ( MAX ( Table1[DATE] ), FILTER ( ALLEXCEPT ( Table1, Table1[ITEM] ), Table1[DATE] < EARLIER ( Table1[DATE] ) && Table1[VALUE] <> BLANK () ) ) ) RETURN IF ( Table1[VALUE] = BLANK (), CALCULATE ( SUM ( Table1[VALUE] ), FILTER ( ALLEXCEPT ( Table1, Table1[ITEM] ), Table1[DATE] = MyDate ) ), [VALUE] )
This one should also work
Test2 = VAR temp = TOPN ( 1, FILTER ( Table1, Table1[ITEM] = EARLIER ( Table1[ITEM] ) && Table1[DATE] < EARLIER ( Table1[DATE] ) && Table1[VALUE] <> BLANK () ), [DATE], DESC ) RETURN IF ( ISBLANK ( Table1[VALUE] ), MINX ( temp, [VALUE] ), [VALUE] )
Dear Zubar
Many thanks for your solutions. both gives me correct results.
Actually
"FirstNonBlank /LastNonBlank return the first/last value respectively in the column…..after sorting the column in its native Ascending Order….column, filtered by the current context, where the expression is not blank."
SO when you use
LASTNONBLANK ( Table1[VAL], Table1[VAL] )
It returns the lastvalue sorting values in their own order and NOT by the order of their dates
Check this post as well
http://www.excelnaccess.com/using-firstnonblank-lastnonblank-in-dax/
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |