cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sk15227
Post Patron
Post Patron

Static Date Comparison

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_DATEDATE_OPENED
3/17/20203/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!

1 ACCEPTED SOLUTION

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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

View solution in original post

6 REPLIES 6
v-yuta-msft
Community Support
Community Support

@sk15227 ,

 

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

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
Super User
Super User

@sk15227 , 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.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

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

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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

View solution in original post

@amitchandak , I think this works!


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

Capture.PNG

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 😁😁

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors