Forum Discussion
Working with 2 Dates
Hello guys,,
ive been wondering how can i do these
I have a date "Request date" and "Completion date"
I want to see the total request in a month and see the no of completed request that are not rely on request date)
like
MONTH-----------NO of Request ----------No of COmpleted
january-----------------50--------------------------45
February----------------60--------------------------65
Hi @anthonycarreon,
In you scenario, Userelationship function should help.
Here’s my sample data:
Please check following steps as below:
1. Manage relationships between two tables:
2. Create measures:
no of complete = CALCULATE(COUNT('Table'[complete]),USERELATIONSHIP('Date'[Date],'Table'[complete]))
no of request = CALCULATE(COUNT('Table'[request]),USERELATIONSHIP('Date'[Date],'Table'[request]))
3. Result would be shown as below:
BTW, Pbix as attached. Hopefully works for you.
Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
ok, assuming requests and completions happen at different times, they should be in 2 different fact tables. those tables will each have a date column.
if you add a dimension table that is a calendar table you can relate it to both fact tables
if you then make a report and create a slicer on the calendar table, when you select a month in your slicer it will filter both requests and completions to the same month
if you google "calendar table" on youtube you will find lots of videos explaining how to use one, some of which include downloads of a free example
https://www.youtube.com/watch?v=zXZAZrUwUe8&t=38sHi @anthonycarreon,
In you scenario, Userelationship function should help.
Here’s my sample data:
Please check following steps as below:
1. Manage relationships between two tables:
2. Create measures:
no of complete = CALCULATE(COUNT('Table'[complete]),USERELATIONSHIP('Date'[Date],'Table'[complete]))
no of request = CALCULATE(COUNT('Table'[request]),USERELATIONSHIP('Date'[Date],'Table'[request]))
3. Result would be shown as below:
BTW, Pbix as attached. Hopefully works for you.
Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
