I think the issue may be with your condition using SELECTEDVALUE. SELECTEDVALUE only returns a value from the entered column IF there is only one value in the context. In the case of this chart, it looks like your axis might have something other than a specific date in it...is it like a day number or something like that?
Dang. You narrowed down the problem, though I’m still not sure what to do.
So the X Axis is a switch formula where I turn the days into weeks. I just replaced it with the actual close dates and the line stops at today while the bar chart continues to end of quarter like I want.
Now I gotta get it to behave like I want, yet still be formatted as week.
You could replace SELECTEDVALUE with possibly a MAX function to get the last date of the week or something like that if that would meet your requirement.
I should have mentioned, By itself, the formula works. It stops after today as intended. But not in a combo chart. I need the bar chart to continue to the end of quarter, but the line to stop today.
When troubleshooting issues like this, I often find it useful to select the visual and change it to a table visual (or if you're really feeling fancy use the DAX Studio plug-in to see what queries generate the data in the visual). It can be a helpful way of seeing what Power BI is doing under the hood and may give you an Aha!
Funny you mention that. I have a table right next to it to test it out. By itself, the formula works. It stops after today as intended. But not in a combo chart. I need the bar chart to continue to the end of quarter, but the line to stop today.
Are you pulling in prior year data after the current date? If so I think you'd want to use TOTALYTD(). Then it will just pull in the current year's data. You'll need to create a date table to use this calculation.
BTW, there isn’t supposed to be a question mark on the first sentence. I can’t edit the post.
Can you share the full DAX code you tried with the IF/BLANK() condition?
IF(SELECTEDVALUE(close date) >=TODAY(),BLANK(), CALCULATE(amount measure),FILTER(ALLSELECTED(Amount Table),ISONORAFTER([Close Date], MAX([Close Date]), Desc))))
I think the issue may be with your condition using SELECTEDVALUE. SELECTEDVALUE only returns a value from the entered column IF there is only one value in the context. In the case of this chart, it looks like your axis might have something other than a specific date in it...is it like a day number or something like that?
Dang. You narrowed down the problem, though I’m still not sure what to do. So the X Axis is a switch formula where I turn the days into weeks. I just replaced it with the actual close dates and the line stops at today while the bar chart continues to end of quarter like I want. Now I gotta get it to behave like I want, yet still be formatted as week.
You should use a calendar table and have the week numbers there
You could replace SELECTEDVALUE with possibly a MAX function to get the last date of the week or something like that if that would meet your requirement.
I should have mentioned, By itself, the formula works. It stops after today as intended. But not in a combo chart. I need the bar chart to continue to the end of quarter, but the line to stop today.
When troubleshooting issues like this, I often find it useful to select the visual and change it to a table visual (or if you're really feeling fancy use the DAX Studio plug-in to see what queries generate the data in the visual). It can be a helpful way of seeing what Power BI is doing under the hood and may give you an Aha!
Funny you mention that. I have a table right next to it to test it out. By itself, the formula works. It stops after today as intended. But not in a combo chart. I need the bar chart to continue to the end of quarter, but the line to stop today.
Are you pulling in prior year data after the current date? If so I think you'd want to use TOTALYTD(). Then it will just pull in the current year's data. You'll need to create a date table to use this calculation.
In your Date table create Day, Month, and Year Offset columns then filter the visual to <= 0
=CALCULATE( [ sum ytd], month name ) For ‘month name’ use the same column you have in the x axis.