Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code FABINSIDER for a $400 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hello,
I have a date table connected to a fact table which only evaluates a measure on a monthly basis. I'd like to see the last non blank value of that measure returned for every date that I add to a table visual.
So for example, I'd like to be able to return 1.068M for 8/10, 8/17, 8/24 and then 1.059M for 9/7, 9/14 and so forth. Any ideas on how I'd achieve this?
Thanks
Solved! Go to Solution.
Hi @rmod32345 ,
You can try formula like below to create measure:
LastNonBlankMeasure =
VAR LastNonBlankDate =
CALCULATE (
MAX ( 'DateTable'[Date] ),
FILTER (
ALL ( 'DateTable' ),
'DateTable'[Date] <= MAX ( 'DateTable'[Date] )
&& NOT ( ISBLANK ( [total_] ) )
)
)
RETURN
CALCULATE (
[total_],
FILTER ( ALL ( 'DateTable' ), 'DateTable'[Date] = LastNonBlankDate )
)
Best Regards,
Adamk Kong
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @rmod32345 ,
You can try formula like below to create measure:
LastNonBlankMeasure =
VAR LastNonBlankDate =
CALCULATE (
MAX ( 'DateTable'[Date] ),
FILTER (
ALL ( 'DateTable' ),
'DateTable'[Date] <= MAX ( 'DateTable'[Date] )
&& NOT ( ISBLANK ( [total_] ) )
)
)
RETURN
CALCULATE (
[total_],
FILTER ( ALL ( 'DateTable' ), 'DateTable'[Date] = LastNonBlankDate )
)
Best Regards,
Adamk Kong
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@rmod32345 Try this DAX
Would something similar be possible in a measure?
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the February 2025 Power BI update to learn about new features.
User | Count |
---|---|
83 | |
78 | |
52 | |
37 | |
36 |
User | Count |
---|---|
104 | |
85 | |
47 | |
44 | |
42 |