PowerBi timeseries duration





.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}







1















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.










share|improve this question

























  • What results are you expecting? I'm not sure I understand what your bar chart is supposed to show.

    – Alexis Olson
    Nov 16 '18 at 17:05











  • Ideally cumulative duration by end_date or total duration by date will do. Thanks

    – Frank
    Nov 16 '18 at 18:52











  • Can you explain what you mean by that? E.g. what value do you expect for May and why?

    – Alexis Olson
    Nov 16 '18 at 19:41











  • 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
    Nov 16 '18 at 19:59


















1















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.










share|improve this question

























  • What results are you expecting? I'm not sure I understand what your bar chart is supposed to show.

    – Alexis Olson
    Nov 16 '18 at 17:05











  • Ideally cumulative duration by end_date or total duration by date will do. Thanks

    – Frank
    Nov 16 '18 at 18:52











  • Can you explain what you mean by that? E.g. what value do you expect for May and why?

    – Alexis Olson
    Nov 16 '18 at 19:41











  • 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
    Nov 16 '18 at 19:59














1












1








1








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.










share|improve this question
















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.







powerbi dax






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 16 '18 at 19:58







Frank

















asked Nov 16 '18 at 14:21









FrankFrank

528




528













  • What results are you expecting? I'm not sure I understand what your bar chart is supposed to show.

    – Alexis Olson
    Nov 16 '18 at 17:05











  • Ideally cumulative duration by end_date or total duration by date will do. Thanks

    – Frank
    Nov 16 '18 at 18:52











  • Can you explain what you mean by that? E.g. what value do you expect for May and why?

    – Alexis Olson
    Nov 16 '18 at 19:41











  • 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
    Nov 16 '18 at 19:59



















  • What results are you expecting? I'm not sure I understand what your bar chart is supposed to show.

    – Alexis Olson
    Nov 16 '18 at 17:05











  • Ideally cumulative duration by end_date or total duration by date will do. Thanks

    – Frank
    Nov 16 '18 at 18:52











  • Can you explain what you mean by that? E.g. what value do you expect for May and why?

    – Alexis Olson
    Nov 16 '18 at 19:41











  • 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
    Nov 16 '18 at 19:59

















What results are you expecting? I'm not sure I understand what your bar chart is supposed to show.

– Alexis Olson
Nov 16 '18 at 17:05





What results are you expecting? I'm not sure I understand what your bar chart is supposed to show.

– Alexis Olson
Nov 16 '18 at 17:05













Ideally cumulative duration by end_date or total duration by date will do. Thanks

– Frank
Nov 16 '18 at 18:52





Ideally cumulative duration by end_date or total duration by date will do. Thanks

– Frank
Nov 16 '18 at 18:52













Can you explain what you mean by that? E.g. what value do you expect for May and why?

– Alexis Olson
Nov 16 '18 at 19:41





Can you explain what you mean by that? E.g. what value do you expect for May and why?

– Alexis Olson
Nov 16 '18 at 19:41













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
Nov 16 '18 at 19:59





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
Nov 16 '18 at 19:59












1 Answer
1






active

oldest

votes


















0














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






share|improve this answer
























  • It works fine for the entire series, the problem occurs when I filter to a specific record, then the duration is from the start of id 1. :(

    – Frank
    Nov 16 '18 at 22:30











  • Change ALL(Table2) in the first line to ALLSELECTED(Table2).

    – Alexis Olson
    Nov 16 '18 at 22:32











  • Changing both ALL(Table2) -> ALLSELECTED(Table2) does it. Thanks for your help!!

    – Frank
    Nov 16 '18 at 22:35











  • @AlexisOlson when is your book coming out?

    – StelioK
    Nov 17 '18 at 0:10






  • 1





    @StelioK It's a long book. Be sure to at least get through chapters 4 and 5. Those are key.

    – Alexis Olson
    Nov 17 '18 at 1:18












Your Answer






StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});














draft saved

draft discarded


















StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53339676%2fpowerbi-timeseries-duration%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown

























1 Answer
1






active

oldest

votes








1 Answer
1






active

oldest

votes









active

oldest

votes






active

oldest

votes









0














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






share|improve this answer
























  • It works fine for the entire series, the problem occurs when I filter to a specific record, then the duration is from the start of id 1. :(

    – Frank
    Nov 16 '18 at 22:30











  • Change ALL(Table2) in the first line to ALLSELECTED(Table2).

    – Alexis Olson
    Nov 16 '18 at 22:32











  • Changing both ALL(Table2) -> ALLSELECTED(Table2) does it. Thanks for your help!!

    – Frank
    Nov 16 '18 at 22:35











  • @AlexisOlson when is your book coming out?

    – StelioK
    Nov 17 '18 at 0:10






  • 1





    @StelioK It's a long book. Be sure to at least get through chapters 4 and 5. Those are key.

    – Alexis Olson
    Nov 17 '18 at 1:18
















0














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






share|improve this answer
























  • It works fine for the entire series, the problem occurs when I filter to a specific record, then the duration is from the start of id 1. :(

    – Frank
    Nov 16 '18 at 22:30











  • Change ALL(Table2) in the first line to ALLSELECTED(Table2).

    – Alexis Olson
    Nov 16 '18 at 22:32











  • Changing both ALL(Table2) -> ALLSELECTED(Table2) does it. Thanks for your help!!

    – Frank
    Nov 16 '18 at 22:35











  • @AlexisOlson when is your book coming out?

    – StelioK
    Nov 17 '18 at 0:10






  • 1





    @StelioK It's a long book. Be sure to at least get through chapters 4 and 5. Those are key.

    – Alexis Olson
    Nov 17 '18 at 1:18














0












0








0







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






share|improve this answer













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







share|improve this answer












share|improve this answer



share|improve this answer










answered Nov 16 '18 at 22:04









Alexis OlsonAlexis Olson

15.5k22136




15.5k22136













  • It works fine for the entire series, the problem occurs when I filter to a specific record, then the duration is from the start of id 1. :(

    – Frank
    Nov 16 '18 at 22:30











  • Change ALL(Table2) in the first line to ALLSELECTED(Table2).

    – Alexis Olson
    Nov 16 '18 at 22:32











  • Changing both ALL(Table2) -> ALLSELECTED(Table2) does it. Thanks for your help!!

    – Frank
    Nov 16 '18 at 22:35











  • @AlexisOlson when is your book coming out?

    – StelioK
    Nov 17 '18 at 0:10






  • 1





    @StelioK It's a long book. Be sure to at least get through chapters 4 and 5. Those are key.

    – Alexis Olson
    Nov 17 '18 at 1:18



















  • It works fine for the entire series, the problem occurs when I filter to a specific record, then the duration is from the start of id 1. :(

    – Frank
    Nov 16 '18 at 22:30











  • Change ALL(Table2) in the first line to ALLSELECTED(Table2).

    – Alexis Olson
    Nov 16 '18 at 22:32











  • Changing both ALL(Table2) -> ALLSELECTED(Table2) does it. Thanks for your help!!

    – Frank
    Nov 16 '18 at 22:35











  • @AlexisOlson when is your book coming out?

    – StelioK
    Nov 17 '18 at 0:10






  • 1





    @StelioK It's a long book. Be sure to at least get through chapters 4 and 5. Those are key.

    – Alexis Olson
    Nov 17 '18 at 1:18

















It works fine for the entire series, the problem occurs when I filter to a specific record, then the duration is from the start of id 1. :(

– Frank
Nov 16 '18 at 22:30





It works fine for the entire series, the problem occurs when I filter to a specific record, then the duration is from the start of id 1. :(

– Frank
Nov 16 '18 at 22:30













Change ALL(Table2) in the first line to ALLSELECTED(Table2).

– Alexis Olson
Nov 16 '18 at 22:32





Change ALL(Table2) in the first line to ALLSELECTED(Table2).

– Alexis Olson
Nov 16 '18 at 22:32













Changing both ALL(Table2) -> ALLSELECTED(Table2) does it. Thanks for your help!!

– Frank
Nov 16 '18 at 22:35





Changing both ALL(Table2) -> ALLSELECTED(Table2) does it. Thanks for your help!!

– Frank
Nov 16 '18 at 22:35













@AlexisOlson when is your book coming out?

– StelioK
Nov 17 '18 at 0:10





@AlexisOlson when is your book coming out?

– StelioK
Nov 17 '18 at 0:10




1




1





@StelioK It's a long book. Be sure to at least get through chapters 4 and 5. Those are key.

– Alexis Olson
Nov 17 '18 at 1:18





@StelioK It's a long book. Be sure to at least get through chapters 4 and 5. Those are key.

– Alexis Olson
Nov 17 '18 at 1:18




















draft saved

draft discarded




















































Thanks for contributing an answer to Stack Overflow!


  • Please be sure to answer the question. Provide details and share your research!

But avoid



  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.


To learn more, see our tips on writing great answers.




draft saved


draft discarded














StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53339676%2fpowerbi-timeseries-duration%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown





















































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown

































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown







Popular posts from this blog

Florida Star v. B. J. F.

Danny Elfman

Lugert, Oklahoma