I have multiple date columns in one table that are not connected to the Date Table. Trying to come up with DAX formula to get Sales for all jobs that have a Received, Approved & Actual Start Date of the same given date range. Here is a copy of what I have currently, which is the correct totals for each, but trying to get a formula where I can use one date range instead of 3.
Any chance you can send through the file or an example file to work through.
There’s a bit to this and it would be better to show you exactly how I would work it out.
You will want to set up the model in a certain way and then you will have some reasonably advanced formula as well.
You will also in some way want to be using the date table. You always do in nearly every case.
With you filters though are you meant to have all three working at the same time? Because the user could select time frames that don’t overlap and you’ll have a lot of problems with the context within you report page throwing up really odd numbers or even none at all.
Sam, I tried several things today such as TREATAS, but haven’t had any success. Here is a snap shot of what The structure looks like. The 3 dates I want to connect somehow to the Date table comes from the Job_Mgmt_Report Table. Reason why I don’t have that table connecting to the Date table is that there are several dates fields inside that table. Thanks in advance for your guidance.
Ok sure thing.
But you can connect them to the date table, but they will just have to be ‘inactive’ relationships.
You can turn these inactive relationships on by using the USERELATIONSHIP function inside of CALCULATE.
I don’t think you need the TREATAS function for this one.
Though, I’m still not really 100% on what you are attempting to calculate here.
By using inactive relationships though you should be able to do this all with formula.
If I could get more info and potentially see a mock up of the model that would be great.
Is the context for the calculation also ‘Salesperson’?
So you want to select a time frame and then understand which jobs sit within the selected time frame? Is that it.
I think the three filters are confusing me as they certainly aren’t required I don’t believe.
I will try using USERELATIONSHIP function, but here is more insight to what I’m trying to do. Trying to calculate total Sales by Salesperson. In the Job_Mgmt_Report Table, each row of data contains a specific job that has a salesperson attached to it. There are also multiple dates for each job as the job progresses along. I want to calculate total sales for each salesperson for every job that has a Received, Approved & Actual Start Date within the given date range. That is why I need to somehow use DAX to use one date range( instead of the 3 using currently) to calculate how much revenue each salesperson had where the job met the criteria from above. I definitely need all 3 dates, but it will always be calculated in the same date range.
Honestly, this is the video that will help you. Based on what you mentioned above and as Sam suggested, USERRELATIONSHIP is your best bet.
Create a separate sales measure for Received, Approved & Actual Start Date using userrelationship.
I tried using this video this morning. It is a little more complicated than just building 3 separate measures. I don’t want totals for all 3, I want totals for all jobs that have those 3 date fields within the same period of time. I’m getting ready to upload a sample file shortly that will hopefully help Sam figure this out. Thanks for your guidance though.
Sam, trying to upload a sample file for you to work with, but is too big. going to email it to you.
Ok sure.
I’ll have a look.
I though last night also the other technique that I think will solve this is this one.
I’ll review and see if it is.
Came up with these 2 formulas and got correct #. This look right to you? Also attached picture of what table looks like.
What I’m a little confused about here is why do you need long formulas?
The natural context from the date filters are already completing that work for you?
I don’t know what date context giving you those results, but really those entire formulas (the FILTERS I mean) are just replicated what the slicers are doing, so in essence they are doing nothing.
I’m playing around with it here
Just by changing the slicers here you will change the results.
Why are these different to you first example you shared.
Sorry for the confusion here but I just can’t see these new formula are doing anything really, so unsure why now you’re getting the correct results.
In my first post I mentioned that the 4 date slicers gave me the correct results I was looking for, but the end user (my boss) wanted me to come up with a way that we could use only one date slicer. I created the inactive relationships between those 4 date fields and the date table, and then created the calculations in the previous post that ended up giving me the same results as I originally had, but now we are only using 1 date slicer instead of 4. The video you sent regarding sales and orders in progress help me solve the calculation. Sorry for any confusion along the process.
Ok got it now.
Sorry I downloaded the resource and didn’t see any of the relationships setup.
Great. Yes ok I think that you’ve got it right then actually.
Certainly a bit to the formula but it makes sense now and that’s how you would do it.
Let me know if any further issues.
