cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
anthonycarreon Frequent Visitor
Frequent Visitor

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



1 ACCEPTED SOLUTION

Accepted Solutions
v-jayw-msft Established Member
Established Member

Re: Working with 2 Dates

Hi @anthonycarreon,

 

In you scenario, Userelationship function should help.

Here’s my sample data:

1.PNG

Please check following steps as below:

1. Manage relationships between two tables:

3.PNG

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:

2.PNG

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.

View solution in original post

3 REPLIES 3
kentyler Established Member
Established Member

Re: Working with 2 Dates

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=38s

v-jayw-msft Established Member
Established Member

Re: Working with 2 Dates

Hi @anthonycarreon,

 

In you scenario, Userelationship function should help.

Here’s my sample data:

1.PNG

Please check following steps as below:

1. Manage relationships between two tables:

3.PNG

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:

2.PNG

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.

View solution in original post

anthonycarreon Frequent Visitor
Frequent Visitor

Re: Working with 2 Dates

Thanks for you Feedback Sir, 😉
kudos 

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (2,960)