Total Missing (Dax Measure)

DAX DAX Calculations
1

Hi All,

Just wondering someone could help". Working through a logic and total (Actual Revenue) is missing in a table and card.

I’m trying to calculate based on date selected, my measure finds datediff (Months) between "Max date selected and “Revenue Start Date” = “Month Diff”, then multiply by “Monthly Revenue” = “Actual Revenue”

Everything works well but missing “Actual Revenue” grand total in the table and also in the card.

Thanks.

Please attached working file.
Op Tracker.pbix (562.2 KB)

2

@Tee ,

Your problem here is twofold:

First, from a data modeling perspective you don’t need the selected dates table. Remove that table from your model and work off the Dates[Date] field instead.

Second, you’re using “between” slicer configuration, which has two different endpoints defining a range. Using SELECTEDVALUE to try to harvest the date from that slicer will not work, since in that configuration the slicer will return multiple dates, leading to the measure getting a blank value. If you drop your Selected Date measure into a card visual, you’ll see that unless you align the endpoints on the same day, the slicer will always come up blank.

Thus, I would change the slicer to a “before” configuration and use the following DAX to harvest a single value:

Selected Date = 
CALCULATE(
    MAX( Dates[Date] ),
    ALLSELECTED( Dates[Date] )
)

I think once you do that, the rest should fall into place fairly easily.

Here’s a video that I did on this topic, that I think will be helpful to you.

Give this all a go, and if you still are having problems give a shout.

I hope this is helpful.

– Brian

2 Likes
3

Hi Brian,

Thank you so much for your prompt response and very helpful solution…appreciate

4

@Tee ,

Glad to help. One of my minor pet peeves in Power BI is the lack of a single value slider option. It’s availalble if you create a what-if parameter, but that doesn’t work for dates. You can fudge it by using a before or after slider, and then covering the other date input with a blank button. It works, but sometimes looks a little wonky…

  • Brian

image

5

Hello @Tee, good to see that you are having progress with your inquiry. Did the response from @BrianJ help you solve your inquiry? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.

6

Hi @Tee, we’ve noticed that no response has been received from you since Sep 3. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.