Forum Discussion

kieranlane's avatar
kieranlane
New Member
8 years ago

Hours Saved Calculation with Date Slicer

I am working on a Dashboard which will identify how many hours were saved during a specified time period.

 

Using a Sharepoint List, connected to PowerBI, people can add their projects to the list, entering the number of hours the project saves us per Month and whether the time saved is a one-off or re-occurs monthly. For example:

 

PROD 1 = Saves 10 hours worth of work.

PROD 2 = Saves 10 hours worth of work carried out monthly.

 

When using a Date Slicer to look at hours saved over the last 6 months, I can only use the Start date as an Identifier. So therefore on-going projects like PROD 2's hours saved over those 6 months isn't included if the projects start date is outside the range. How do I create a Slicer so that when selecting a 6 month period/any time period, The hours saved during those months, and only those months, are included across all on-going projects and standalone ones.

 

Note this also works the other way around, for example, if the on-going project's start date is within range, all hours ever saved by the on-going projects, even those saved outside the date range are included in the total. :S Any help with this would be greatly appreciated, every solution I can think of brings me back to using the filter selected start/end dates but there no apparent way to reference that as a variable in a measure?

 

See Also: https://stackoverflow.com/questions/48462895/including-re-occurring-time-saved-in-date-slicer-output and Visual Rep of problem below:

 

Visual Representation

2 Replies

  • I am working on a Dashboard which will identify how many hours were saved during a specified time period.

     

    Using a Sharepoint List, connected to PowerBI, people can add their projects to the list, entering the number of hours the project saves us per Month and whether the time saved is a one-off or re-occurs monthly. For example:

     

    PROD 1 = Saves 10 hours worth of work.

    PROD 2 = Saves 10 hours worth of work carried out monthly.

     

    When using a Date Slicer to look at hours saved over the last 6 months, I can only use the Start date as an Identifier. So therefore on-going projects like PROD 2's hours saved over those 6 months isn't included if the projects start date is outside the range. How do I create a Slicer so that when selecting a 6 month period/any time period, The hours saved during those months, and only those months, are included across all on-going projects and standalone ones.

     

    Note this also works the other way around, for example, if the on-going project's start date is within range, all hours ever saved by the on-going projects, even those saved outside the date range are included in the total. :S Any help with this would be greatly appreciated, every solution I can think of brings me back to using the filter selected start/end dates but there no apparent way to reference that as a variable in a measure?

     

    See Also: https://stackoverflow.com/questions/48462895/including-re-occurring-time-saved-in-date-slicer-output and Visual Rep of problem below:

     

    Visual Representation

    • Anonymous's avatar
      Anonymous
      Not applicable

      Hi kieranlane,

       

      Based on your description and screenshots, I'd like to suggest you to use gantt visual, you can import this visual from store.

       

       

      >>Any help with this would be greatly appreciated, every solution I can think of brings me back to using the filter selected start/end dates but there no apparent way to reference that as a variable in a measure?

      You can try to use 'selectedvalue' function to get selected value from external tables.

       

      Regards,

      Xiaoxin Sheng