The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now
Hello,
i am trying to get the last value from a date. This value should be use for the month before which are null.
The initial situation looks like that:
After a DAX-Function the result should look like this:
About help or solution suggestions, I would be very happy!
Thanks,
alex
Solved! Go to Solution.
HI, @Devilstar
You could try this formula to add a column as below:
Value 2 = VAR CurrentCar = 'Table'[Car] VAR CurrentDate = 'Table'[Month] VAR CurrentRegion = 'Table'[region] VAR LastDateWithValue = IF ( ISBLANK ( 'Table'[Value] ) = FALSE (), 'Table'[month], CALCULATE ( MIN ( 'Table'[month] ), FILTER ( 'Table', 'Table'[car] = EARLIER ( 'Table'[car] ) && 'Table'[region] = EARLIER ( 'Table'[region] ) &&'Table'[month]>=EARLIER('Table'[month]) && ISBLANK ( 'Table'[Value] ) = FALSE () ) ) ) RETURN VAR A = CALCULATE ( LASTNONBLANK( 'Table'[Value], 'Table'[Value] ), FILTER ( 'Table', 'Table'[Car] = CurrentCar && 'Table'[Month] = LastDateWithValue && 'Table'[region] = CurrentRegion ) ) RETURN IF ( ISBLANK ( 'Table'[Value] ), A, 'Table'[Value] )
Result:
Best Regards,
Lin
Looks like a good candidate for Fill Up in the Power Query Editor(Transform).
It only works for null entries, remember so you may have to use Replace first.
thank you for your suggestion! With the function Fill Up it works (with import from SAP BW).
But i want to use in the next time direct query (on SAP BW), so i have to solve it with a function.
On this topic there is nearly the same problem case:
https://community.powerbi.com/t5/Desktop/Fill-blanks-with-previous-value/m-p/492572#M229548
i change there solotion for my case:
Value2 = VAR CurrentCar = 'Table'[Car] VAR CurrentDate = 'Table'[Month] VAR CurrentRegion = 'Table'[region] VAR LastDateWithValue = CALCULATE ( MAX ( 'Table'[Month] ); FILTER ( 'Table'; 'Table'[Value] <> BLANK () && 'Table'[Car] = CurrentCar && 'Table'[Month] = CurrentDate && 'Table'[region] = CurrentRegion ) ) Return CALCULATE ( LASTNONBLANK('Table'[Value];'Table'[Value]); FILTER ( 'Table'; 'Table'[Car] = CurrentCar && 'Table'[Month] > LastDateWithValue && 'Table'[region] = CurrentRegion ) )
Now it looks like that:
Unfortunately, it is not the solution yet....
if would like to have a formula like this:
value3 = if('Table'[Value]=BLANK();"get last Value";"nothing")
Have anyone a solution?
Thanks,
alex
HI, @Devilstar
You could try this formula to add a column as below:
Value 2 = VAR CurrentCar = 'Table'[Car] VAR CurrentDate = 'Table'[Month] VAR CurrentRegion = 'Table'[region] VAR LastDateWithValue = IF ( ISBLANK ( 'Table'[Value] ) = FALSE (), 'Table'[month], CALCULATE ( MIN ( 'Table'[month] ), FILTER ( 'Table', 'Table'[car] = EARLIER ( 'Table'[car] ) && 'Table'[region] = EARLIER ( 'Table'[region] ) &&'Table'[month]>=EARLIER('Table'[month]) && ISBLANK ( 'Table'[Value] ) = FALSE () ) ) ) RETURN VAR A = CALCULATE ( LASTNONBLANK( 'Table'[Value], 'Table'[Value] ), FILTER ( 'Table', 'Table'[Car] = CurrentCar && 'Table'[Month] = LastDateWithValue && 'Table'[region] = CurrentRegion ) ) RETURN IF ( ISBLANK ( 'Table'[Value] ), A, 'Table'[Value] )
Result:
Best Regards,
Lin
thanks! This works 🙂
Best regards
Alex
Hi @Devilstar,
You can use the below DAX to create the custom column.
Value2 = VAR CurrentCar = 'Table'[Car] VAR CurrentDate = 'Table'[Month] VAR CurrentRegion = 'Table'[region] VAR _LastDateWithValue = CALCULATE ( MIN ( 'Table'[Month] ), FILTER ( 'Table', 'Table'[Value] <> BLANK () && 'Table'[Car] = CurrentCar && 'Table'[Month] > CurrentDate && 'Table'[region] = CurrentRegion ) ) var _result= IF(ISBLANK('Table'[Value]), CALCULATE ( FIRSTNONBLANK('Table'[Value],1), FILTER ( 'Table', 'Table'[Car] = CurrentCar && 'Table'[Month] = _LastDateWithValue && 'Table'[region] = CurrentRegion ) ),'Table'[Value]) Return _result
Below is the result I have got from the above expression.
You can see the pbix file for your reference.
If this helped you, please mark this post as an accepted solution and like to give KUDOS .
Regards,
Affan
Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.
User | Count |
---|---|
158 | |
106 | |
96 | |
83 | |
75 |
User | Count |
---|---|
153 | |
137 | |
131 | |
81 | |
61 |