How to Filter jobs and data based on different dates

[JS_07216]

I have a model with a calendar created to include Financial Year. In a separate table I have multiple dates associated with completing a task for each job. eg start date and end date.

I want to be able to select the jobs and the data based on the jobs with a start date in a certain financial year and then in a separate report select the jobs using a different task date. I have tried userelationship but I am new at this.

Table 1 - Calendar Table

Table 2 - Task Dates

Table 3 - Jobs and Transaction data

The end result is I want a matrix which has columns of data for a job and rows which group the jobs by month of the relevant task date within the filtered Financial Year

[v-hjannapu · Accepted solution]

Hi @JS_07216,

Thank you  for reaching out to the Microsoft fabric community forum.
Thank you @bhanu_gautam , for your response regarding the issue.

connect your Calendar table to both the Start Date and End Date columns in the Task Dates table using inactive relationships. Then, use a Matrix visual with the Financial Month in rows, and add measures that activate either the Start or End Date relationship using USERELATIONSHIP. Finally, apply a slicer on Financial Year to filter the results as needed.
find the attached Pbix file for your reference

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  
If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it

Best Regards,
Harshitha.
Community Support Team

[v-hjannapu · Accepted solution]

Hi @JS_07216,

Thank you  for reaching out to the Microsoft fabric community forum.
Thank you @bhanu_gautam , for your response regarding the issue.

connect your Calendar table to both the Start Date and End Date columns in the Task Dates table using inactive relationships. Then, use a Matrix visual with the Financial Month in rows, and add measures that activate either the Start or End Date relationship using USERELATIONSHIP. Finally, apply a slicer on Financial Year to filter the results as needed.
find the attached Pbix file for your reference

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  
If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it

Best Regards,
Harshitha.
Community Support Team

[JS_07216]

Thank you  Team,

It allows me to count as list the jobs as required but I cant seem to group other information such as costs per job. It collates all information and replicates it

Thanks again

[v-hjannapu · Accepted solution]

Hi @JS_07216,

This usually happens when the matrix visual does not include a column such as Job ID or task-level granularity to group the costs correctly, or when the measure doesn’t take the correct task date relationship into account.
Update the Measure to Use the Right Task Date

Margin by Start Date :=
CALCULATE(
    SUM('Jobs and Transaction Data'[Current Margin Ex GST]),
    USERELATIONSHIP('Calendar'[Date], 'Task Dates'[Start Date])
)

To make sure your data is grouped correctly by month, keep using the Year Mth or Month column from your Calendar table in the matrix rows .If you want to break the data down further  you can also add the Job ID from your jobs table into the rows. This will help avoid showing the same total for every row and give a clearer picture of costs per job per month.

Also, make sure you're using a slicer for FinancialYear from the Calendar table on your report page. This lets you easily filter everything in the report including your matrix  to show only data for the selected financial year. It helps keep your results relevant and focused.

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it

Best Regards,
Harshitha.
Community Support Team

[JS_07216]

@v-hjannapu thank you again. From here I assume that if I want additional data for a job I need to create separate measures eg Cost Of Sales or Revenue for the job need separate measures using similar calculations.

Thanks

[v-hjannapu]

Hi @JS_07216,

I'm glad to hear that the solution worked for you, If your issue is resolved, please consider Accept it as solution so that other community members can easily find it as well.

Best Regards,
Harshitha.
Community Support Team

[v-hjannapu]

Hi @JS_07216,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please Accept it as a solution so that other community members can find it easily.

Best Regards,
Harshitha.
Community Support Team

[v-hjannapu]

Hi @JS_07216,

May I ask if you have resolved this issue? If so, please mark the helpful reply and Accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Best Regards,
Harshitha.
Community Support Team

[bhanu_gautam]

@JS_07216 

Ensure you have the following relationships:

Calendar[Date] to Task Dates[Start Date]
Calendar[Date] to Task Dates[End Date]

Create measures to filter jobs based on the start date and other task dates.

JobsWithStartDateInFY =
CALCULATE(
COUNTROWS('Jobs and Transaction data'),
USERELATIONSHIP('Calendar'[Date], 'Task Dates'[Start Date]),
'Calendar'[FinancialYear] = SELECTEDVALUE('Calendar'[FinancialYear])
)

Measure for Jobs with End Date in Financial Year

DAX
JobsWithEndDateInFY =
CALCULATE(
COUNTROWS('Jobs and Transaction data'),
USERELATIONSHIP('Calendar'[Date], 'Task Dates'[End Date]),
'Calendar'[FinancialYear] = SELECTEDVALUE('Calendar'[FinancialYear])
)

Add a Matrix visual to your report.
Use the FinancialYear and Month from the Calendar Table as rows.
Use the measures JobsWithStartDateInFY and JobsWithEndDateInFY as values.