1

I am looking into utilising PowerBI to identify time saved due to various Projects. People will add the projects to a Sharepoint List which then feeds into PowerBI.

PROJECTs Table:

Project Tite, Desc, Hours/Month Saved, StartDate, EndDate, Repeat? (T/F)

[Some Projects only save a fixed 10 or so hours, others save time per month (indicated by the Repeat Column)]

I've created two measures, RUNTIME determining how long the project has run in months ((TodayDate - StartDate)/30) as well as TIMESAVED which is the total hours saved from that specific project (RUNTIME*Hours/Month Saved).

Whilst this works, it has a pretty big limitation. When selecting a range, say 01/01/2017 - 01/01/2018, any projects with a start date before that range are excluded. However these maybe on-going, meaning the time saved by this project during the range needs to be added.

I've attempted to find a solution to this, however I keep getting stuck at requiring the the filter dates from the slicer, however I'm not certain this is possible. I need those projects with on-going savings to have the savings during the period given to be counted as well.

Possible alternative maybe to create a Month/Year column per Month/Year with a custom formula per column to determine that projects Hours saved for that Month/Year however this seems inefficient, at that point back to Excel might be better.

Any ideas / suggestions would be greatly appreciated, currently running through any ideas to solve but keeps coming back to needing that value specified by the filter. Cheers in advance for any advice tackling this :)

See also: https://community.powerbi.com/t5/Desktop/Re-occuring-Savings-over-Time-with-Time-Date-Slicer/m-p/346100

Visual Representation of Problem

virtualdvid
  • 2,323
  • 3
  • 14
  • 32
Kieran Lane
  • 11
  • 2

2 Answers2

0

Unfortunately, there is no current simple solution to this problem out-of-the-box with Power Bi. All of the slicers seem to handle dates as a single point in time. They suffer in that if you are dealing with any items that span a Start and End date (like your projects, and most of my data examples) they only take one of the dates as the input. The slicers need to accept an optional end date in our case and then perform a simple date span overlap logic to determine the items that match.

I tried to solve your problem with out-of-the-box Power Bi Desktop slicers and a custom visual Timeline Slicer I found at the store with no luck earlier this month. Out of frustration, I posted a question in the Power Bi forums for suggestions.

The final suggestion from the forums I got was to "use two Filters at Filter pane". But I am not satisfied with this answer.

The Timeline Slicer code is open source and when I get more time (ha ha), I would like to make this change to the Timeline Slicer and publish it back to the repository for everyone to use.

I will monitor this question and the forum to see if a solution emerges in the future.

Taterhead
  • 5,763
  • 4
  • 31
  • 40
  • 1
    Thanks for taking a look, I hadn't considered multiple slicers, utilising both the Start and End date, however with me going about it via the DAX functions I'd still need access to the date value selected in each slicer to then use in that DAX formula. Using a custom visual to achieve this sounds interesting, I'll have a look into how they work maybe there a solution there that'd just require a bit of tinkering. Cheers again, find anything useful give me a shout will be much appreciated! – Kieran Lane Jan 26 '18 at 16:17
  • Upon further review, I think your DAX formula is a separate issue to the slicer missing the date range. However, I'm confident if we can change the slicers to detect data that spans a start and end date, we can get your DAX formula to work also. One problem at a time ;) With PowerBi's monthly release schedule, I'm hoping we can get a solution to help us both eventually. – Taterhead Jan 26 '18 at 16:30
0

You can use Timeline Storyteller. you can create your time line and add a couple Slicers for Start and End. It will split by day the dates and you won't miss any data.

virtualdvid
  • 2,323
  • 3
  • 14
  • 32
  • its encouraging to see the visual use both start and end. I wish it were a simple slicer instead of a the storyteller. – Taterhead Jan 28 '18 at 22:36
  • Interesting, this does look good - I'll give it a test to see if it can output the kind of information I'm looking for. This is very visual, wondering if it allows you to then, once the date range slicer is added, give you numerical outputs alongside the visual gnatt-like chart. – Kieran Lane Jan 29 '18 at 16:40