PowerBi timeseries duration

1
votes

In PowerBI, I have a simple table with 3 columns:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TYzBCcAwDAN38dugyk5bdxaT/deICzXN77hDyhSKCkHYwafwbJyaYiUc9I4XaH/1MgHeXQO+bcf7ux0XW3x5Lg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [id = _t, start = _t, end = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"start", type date}, {"end", type date}})
in
    #"Changed Type"

From which I can create the following visual

TimeLine My challenge is to calculate the total duration in days of the times series based on filter selected above. Any help would be appreciated.

I have tried the following DAX formula but it gives me crazy results as shown above.

YTDDuration = 
var start_Date=FIRSTDATE(ALLSELECTED('CALENDAR'[Date]))
var end_Date=LASTDATE(ALLSELECTED('CALENDAR'[Date]))
var current_Start=MAX(Table2[start])
var current_end=MAX(Table2[end])
var bigif=IF(current_end>start_Date&&current_Start<end_Date,DATEDIFF(MAX(start_Date,current_Start),MIN(end_Date,current_end),DAY),0)
RETURN
CALCULATE(SUMX(Table2, bigif),FILTER(ALL(Table2), Table2[start] <= max(Table2[end])))

Expected output would be: TimeLine

The key here is to account for gaps in dates and consolidate overlapping dates.

1
powerbidax
What results are you expecting? I'm not sure I understand what your bar chart is supposed to show. - Alexis Olson
Ideally cumulative duration by end_date or total duration by date will do. Thanks - Frank
Can you explain what you mean by that? E.g. what value do you expect for May and why? - Alexis Olson
I have added expected output, the key here is day difference between the first date and the end date, accounting for gaps in dates. - Frank

1 Answers

0
votes

You can iterate through your calendar table and count the number of days where that day falls into one of the id time periods.

YTDDuration = 
var current_Start = CALCULATE(MIN(Table2[start]), ALL(Table2))
var current_end = MAX(Table2[end])
RETURN
    SUMX(
        FILTER('CALENDAR', 'CALENDAR'[Date] <= current_end),
        MAXX(ALL(Table2), IF([Date] > [start] && [Date] <= [end], 1, 0))
    )

This starts at the minimal start date and adds 1 for each day where that Date is between start and end for some row in Table2. If none of the rows contains that Date, the max is over just zeros and returns zero. If one or more matches, you get a max of one and we count that day.

YTD Duration by end:

YTD Duration