PowerBi timeseries duration
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
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
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&¤t_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: 
The key here is to account for gaps in dates and consolidate overlapping dates.
powerbi dax
asked Nov 16 '18 at 14:21
FrankFrank
528
add a comment |
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
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&¤t_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:
The key here is to account for gaps in dates and consolidate overlapping dates.
powerbi dax
asked Nov 16 '18 at 14:21
FrankFrank
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
add a comment |
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
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&¤t_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:
The key here is to account for gaps in dates and consolidate overlapping dates.
powerbi dax
asked Nov 16 '18 at 14:21
FrankFrank
528
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
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&¤t_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:
The key here is to account for gaps in dates and consolidate overlapping dates.
powerbi dax
powerbi dax
asked Nov 16 '18 at 14:21
FrankFrank
528
asked Nov 16 '18 at 14:21
FrankFrank
528
edited Nov 16 '18 at 19:58
Frank
asked Nov 16 '18 at 14:21
FrankFrank
528
asked Nov 16 '18 at 14:21
FrankFrank
528
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
add a comment |
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
add a comment |
1 Answer
1
active
oldest
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:
answered Nov 16 '18 at 22:04
Alexis OlsonAlexis Olson
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
ChangeALL(Table2)in the first line toALLSELECTED(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
|
show 2 more comments
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
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
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:
answered Nov 16 '18 at 22:04
Alexis OlsonAlexis Olson
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
ChangeALL(Table2)in the first line toALLSELECTED(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
|
show 2 more comments
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:
answered Nov 16 '18 at 22:04
Alexis OlsonAlexis Olson
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
ChangeALL(Table2)in the first line toALLSELECTED(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
|
show 2 more comments
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:
answered Nov 16 '18 at 22:04
Alexis OlsonAlexis Olson
15.5k22136
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:
answered Nov 16 '18 at 22:04
Alexis OlsonAlexis Olson
15.5k22136
answered Nov 16 '18 at 22:04
Alexis OlsonAlexis Olson
15.5k22136
answered Nov 16 '18 at 22:04
Alexis OlsonAlexis Olson
15.5k22136
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
ChangeALL(Table2)in the first line toALLSELECTED(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
|
show 2 more comments
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
ChangeALL(Table2)in the first line toALLSELECTED(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
|
show 2 more comments
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.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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
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