Static Date Comparison

[Anonymous]

Hi,

I am trying to have a static date table, which I have created, and compare this to a Query pull table. 

I am trying to see that for example, on March 19, 2020, how many Work Requests are "Open". This meaning that a Work Request has been created on or before March 19, 2020, and has not been completed before March 19, 2020.

REPAIR_DATE	DATE_OPENED
3/17/2020	3/16/2020
3/19/2020	3/13/2020
3/19/2020	3/19/2020
3/20/2020	3/18/2020
3/27/2020	3/18/2020
4/9/2020	3/19/2020

So in the table above, the number of "Open" Work Requests would be 1 as of 3/19/2020

I am having trouble creating measures in my Date table to compare to the Query pull table, or to adjust based on a date being used in the dashboard filter

Please let me know if this question does not make any sense

Thank you!

[v-yuta-msft]

@Anonymous ,

So in the table above, the number of "Open" Work Requests would be 1 as of 3/19/2020

I am having trouble creating measures in my Date table to compare to the Query pull table, or to adjust based on a date being used in the dashboard filter

---

I'm confused on your description. Could you share the sample data of Query pull table and clarify more details about the logic of calculating "Open" Work Requests?

Regards,

Jimmy Tao

[Anonymous]

Hi @v-yuta-msft ,

the Opened Work Request just means the date that the Work Request was recieved in the system. There is no calculation behind it

My question is how to show the number of Work Requests that are incomplete on a certain day.. if that makes sense

Thank you!
Sarah

[amitchandak]

@Anonymous , Is it different from the one you asked: https://community.powerbi.com/t5/Desktop/How-Long-Between-Dates/m-p/1022826#M482762

I posted a solution pbix That should work for this one too.

[Anonymous]

hi @amitchandak , I dont believe these are the same questions...

Although the original question you answered does allow me to calculate how many repairs there were in a given day, it does not show me how many are "Open" on any given day

I want to try to create a formula or column that shows the amount of Work Requests that are open on any given day, instead of how long it is between dates

Thank you!
Sarah

[amitchandak · Accepted solution]

@Anonymous , I added two more measures. Hope they will help. File is attached

[Anonymous]

@amitchandak , I think this works!

(I just have to figure out how to subtract from the date before... 

so 2849+7 for 1/1/2014, 2856+13 for 1/2/2014, 2869+(-5) for 1/3/2014, etc... I know that I could possibly do this if i added an INDEX column, but since my access to the data source is messed up, I will need to find a work around.. )

Anyways, thank you so much!
But for now hopefully this answers my question 😁😁