Forum Discussion

Mark1982's avatar
Mark1982
Helper I
2 years ago

Summarize based on date check

Dear friends.

 

Have this headbreaker. Working and pondering on it for some time now. I cannot seem to find a good approach. Although the requirement seem to be straight forward.

 

Have a table with jobs and costs. This includes a dimension (PRODGRP) column.

 

Now the requirement is for every PRODGRP sum the costs when the project is changed within a certain date range. On job level this is working fine. But it fails on PRODGRP level in case there are multiple jobs changed. See below a demo test report. The table in the left bottom is incorrect when moving the date slicer.

 

https://1drv.ms/u/s!Ag3hOdYVKV71t0tOoDaG1WQ5c6Hf?e=hC1jiG

 

Your help will be appricated. Lots of kuddos.

 

 

Need Help

2 Replies

  • JoeBarry's avatar
    JoeBarry
    Solution Sage
    2 years ago

    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

    • Mark1982's avatar
      Mark1982
      Helper I
      2 years ago

      Hi Joe,

       

      Many thanks for helping me here.

       

      But (yeah Iam sorry :)) your solution does not seem to work correctly. The requirements is to only show the last cost within the change table or (if non exists) the job cost based on the date created (within period). This also applies to the PRODGRP, whereby the group should only include costs for the related job within that group.

      Implementing your chages does not provide the desired output. Have updated the pbix on the file share. You will see job 1 is not correct on that basis. 

      This is a tricky one.