Help implementing this Excel example - Count items between date ranges

[Synik_PBI]

Greetings all, 

I tend to get stuck on problems with dates and one has three dates! Here is what the dataset looks like: 

And here is a pivot example extracted from the data above:

Request Submitted = =COUNTIFS(B:B,">="&F2,B:B,"<="&G2)
this is counting the Submitted Date and if it is between the start month and end month, it is counted. 

Request Closed = =COUNTIFS(D:D,">="&F2,D:D,"<="&G2)

Same thing as submitted, but looking at the Completed Date column to come up with the counts. 

Backlog = =(COUNTIFS(B:B,"<="&G2,A:A,"<>Completed",A:A,"<>Canceled"))+(COUNTIFS(B:B,"<="&G2,D:D,">"&G2))

Counts if less than the End Month date and is in NOT in cancelled/completed Status + those in the cancelled/completed Status with a Submitted Date less than the End Month and the Completed Date greater than the End Month.

This pivot results in a visual that looks like this: 

Roadblocks for me: I think the thing that is throwing me off the most is that the Start Month and End Month in the pivot chart have been manually entered so I'm having a ton of difficulty trying to tie something manually entered, even a date, back into the data. I'm not sure if it can be done or if I need to use a date that is already in the dataset. 

Things I've Tried: I have tried multiple angles from this including a column using SWITCH to bucket the range of values into the months, but this didn't work when I tested making the 2nd column for the End Date and noticed the counts fell appart immediately inside a visual. 
I am currently attempting to do a date table and bring in the counts of those Excel formulas into that date table, but I wanted to see if this is the best way to do this. I tend ot overcomplecate things with dates and this one has so many dates! 

Appreciate any help on this! 

[Wilson_]

Hello Synik,

I just wanted to confirm that you are doing the right thing by trying to create a date table to link up to your data. That is indeed a best practice. 🙂

This is my favourite date table at the moment!

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

[Synik_PBI]

Thanks for the reply, @Wilson_ , I did have a follow-up question. 

Its great that I'm at least on the right track, but I'm uncertain how exactly to set up the Date table. I mean, yes, I have the DAX all sorted to create the dates itself, but its the logic with the counts that's confusing me.

See, I'm thinking that I shouldn't create a relationship to one of the existing date fields in m data above because then all the dates in the date table are now related to the Submitted Date (the ideal case since not every Status has a Completed Date populated), and instead not the manually entered date ranged that the Excell example has for the Start/End. 

I was playing around with using things like RELATEDTABLE, but wasn't able to bring in a column from the other table without a relationship. 

You can probably see that I'm overcomplicating this, but I'm not sure logically how this is supposed to work with the 3 date fields. At least I can keep playing around with a date table to see if  can get it to work, but yeah, still confused. 

[Wilson_]

Synik,

Happy to help further. You should set up relationships between the date table and your dates. Only one of those relationships can be considered active by default though, as you noted. However, you can write a measure using DAX like CALCULATE ( ..., USERELATIONSHIP( ) ) to use a different relationship within that specific measure.

Can you please share a sample pbix file? (If you don't know how, please check the pinned thread in the forum.) It would probably make it easier to explain.

[Synik_PBI]

Thanks @Wilson_ 
I have uploaded a simple copy of the test data I'm working with. You can find it on my Google Drive: 
https://drive.google.com/file/d/1ArLqDwbxZiZOxTHRtTkbk7hgAGmJcjrb/view?usp=drive_link

[Wilson_ · Accepted solution]

Hi Synik,

Here is my updated copy of your pbix.

Two notes:

• I did have to add a couple columns with just the date part of your date columns to get the date table relationship to work.
• My numbers don't tie exactly to yours. I'm guessing the numbers used in your screenshot and in your pbix file are not an exact copy.

I added comments to the measures I created so hopefully that helps. 😄

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

[Synik_PBI]

Thanks @Wilson_ , this is amazing! 

I'm trying to digest this now and learn....I'm going to convert this logic over to the live data and will send a message again if I hit a snag or would like some clarification. There's some functions in here like USERELATIONSHIP and ROUNDDOWN that I've never used before. 

[Wilson_]

Synik,

Love it, happy to be of service! 😄

[Synik_PBI]

@Wilson_ 
I have finished implementing the code in the live data and its working, so, many thanks. I'd love to buy you a beer if possible, as I've been stuck on this thing all week! 

Thanks so much!

[Wilson_]

Synik,

Perfect, thanks for the update. Glad to know it worked for you! 🙂