DATEDIFF Final Aggregate

DAX DAX Calculations
1

Hey yal,

I am stuck with a specific measure and wondering if someone here have any guidance.

I am putting a measure together that identifies two specific dates, located in different tables in the data model, and calculate the number of days between the two dates; i am referring to this measure as [POP Days]. Not only do I want to determine the total number of days between the two dates, i want to return the final median aggregate for a context of rows. As you can see below, the measure is working just fine for each individual row however the final aggregate is showing the number of days for the first date for each column. I tried to establish row context by using variables and a virtual table but am still not successful. Any guidance would be greatly appreciated.

2020-02-23_POPDays
2020-02-23_POPDays.png1794Ă—849 81.7 KB

2

Looks like a really interesting formula here.

From what you’ve explained the total you need requires very different logic to what you are calculating at every single row in the table

If that is the case you need to utilise an IF statement to basically put a different formula in for the total specifically.

Check out this video below where this is done.

You see that I’m using the function HASONEVALUE to isolate the total. You could also use ISFILTERED also.

Then when you are working out the total logic that’s when you can use the MEDIAN function, or MEDIANX function.

I honestly think you’re very close this is just the only part I don’t see and would recommend trying to implement.

Thanks
Sam

1 Like
3

Thanks Sam for the reference and the support. I have watched the video and your logic makes sense. Once I get back to my computer, I’ll get back on the report to incorporate this method in the Dax and let you know what I come up with.

4

Hi @JoeRobert, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!"

5

You know how when you are staring at a measure for a long time and for the life of you you cannot figure out how to write it correctly. Then after taking a break from looking at you, you are able to solve the formula after a short time period? Well that is what just happened to me here.

I simplified the measure by removing the variables and branching the individual measures in the formula and I am getting the correct median value as the final aggregate. I cannot explain why variables do not give you the same response as branching measures, maybe one day I will figure it out.

See below screenshot the final measure where I am getting the correct median value for the final aggregate.

2020-03-03_screenshot
2020-03-03_screenshot1229Ă—581 38.2 KB

6

@JoeRobert,

You know how when you are staring at a measure for a long time and for the life of you you cannot figure out how to write it correctly. Then after taking a break from looking at you, you are able to solve the formula after a short time period?

Yup, happens to me all the time.

I suspect this may help you solve the mystery of why the measure worked when the variables didn’t:

Glad to hear you got it working well though.

  • Brian