Summarize based on date check

Hi Mark1982 

I took a look at the dataset. Do the costs from the Job table also have to be included? I will base my answer that they do.

• Please convert your Date Table into a Date Table and choose the Date column
   

   

• Remove the relationship between Jobs and Date and Activate the Relationship between Changes and date.
• Open Power Query and create a duplicate of the Jobs table and rename it DIM Jobs or somehthing similar
• Remove the Cost and Date Created Columns
• Load back into the report
• Create a one to many relationship between DIM Job and Changes on the No_ & Job column
• Create a Many to Many relationship between DIM Job and Jobs table on the PRODGRP column, change the Cross filter direction to DIM Job filters Jobs (This is only done becasue the Jobs table is unique) otherwise one to many is standard
• Create a one to many relationship bewteen the date table and the Job table on the Date columns

Ok the dataset is set up now to be able to use data from the same table

Create the following measure to get what you need.

Job costs =

VAR _JobCost = SUM('Jobs'[Cost])
VAR _ChangeCost = SUM(['Changes'[Cost])

RETURN

_JobCost + _ChangeCost

• Create a table visual
• Add from the DIM Job table the PRODGRP column (You can also add other columns like date from the date Table)
• Then add the new measure
• Create a Slicer with the Date column from the date table

You can then choose a date range that will change the value of the measure based on the date range

Thanks

Joe

If this post helps, then please Accept it as the solution