Forum Discussion
Measure for a Conditional Running Total Between Two Date Fields
Hi Anonymous
Calculating this with measure would be very slow on a large data sets, I suggest you consider a different approach.
You can use Query editor to create a line for every day like ( M code ) below.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQpyDfb3CXN1ATINzfQNzPSNDAwtQRxDOCdWJ1rJCE2pOW6lxqhKjQyQlRqjKDVBU2qIrNQSRakpUMg/wNUPSKEaCpI0Q5U0QZE0h1ji6hjk7AFVYI6iwAJTgQVCQSwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [POSITION_BREAK_ID = _t, BREAK_STATUS_CD = _t, BREAK_RESOLUTION_DT = _t, CREATE_DT_TM = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"POSITION_BREAK_ID", Int64.Type}, {"BREAK_STATUS_CD", type text}, {"BREAK_RESOLUTION_DT", type date}, {"CREATE_DT_TM", type date}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Date", each List.Dates( [CREATE_DT_TM], Duration.Days( ( if [BREAK_RESOLUTION_DT] = null then DateTime.Date( DateTime.LocalNow() ) else [BREAK_RESOLUTION_DT] ) - [CREATE_DT_TM] ), #duration( 1, 0, 0, 0 ) )), #"Expanded Date" = Table.ExpandListColumn(#"Added Custom", "Date"), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Date",{{"Date", type date}}), #"Removed Other Columns" = Table.SelectColumns(#"Changed Type1",{"POSITION_BREAK_ID", "Date", "BREAK_STATUS_CD"}) in #"Removed Other Columns"Next step would be joining Date from your new table with date in your date dimension in the modeling tab and creating a Measure as below.
count = DISTINCTCOUNT( BreakTable[POSITION_BREAK_ID] )
Hope this make sense.
Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.
Hi Anonymous
Calculating this with measure would be very slow on a large data sets, I suggest you consider a different approach.
You can use Query editor to create a line for every day like ( M code ) below.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQpyDfb3CXN1ATINzfQNzPSNDAwtQRxDOCdWJ1rJCE2pOW6lxqhKjQyQlRqjKDVBU2qIrNQSRakpUMg/wNUPSKEaCpI0Q5U0QZE0h1ji6hjk7AFVYI6iwAJTgQVCQSwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [POSITION_BREAK_ID = _t, BREAK_STATUS_CD = _t, BREAK_RESOLUTION_DT = _t, CREATE_DT_TM = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"POSITION_BREAK_ID", Int64.Type}, {"BREAK_STATUS_CD", type text}, {"BREAK_RESOLUTION_DT", type date}, {"CREATE_DT_TM", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Date", each List.Dates( [CREATE_DT_TM], Duration.Days( ( if [BREAK_RESOLUTION_DT] = null then DateTime.Date( DateTime.LocalNow() ) else [BREAK_RESOLUTION_DT] ) - [CREATE_DT_TM] ), #duration( 1, 0, 0, 0 ) )),
#"Expanded Date" = Table.ExpandListColumn(#"Added Custom", "Date"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Date",{{"Date", type date}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type1",{"POSITION_BREAK_ID", "Date", "BREAK_STATUS_CD"})
in
#"Removed Other Columns"Next step would be joining Date from your new table with date in your date dimension in the modeling tab and creating a Measure as below.
count = DISTINCTCOUNT( BreakTable[POSITION_BREAK_ID] )
Hope this make sense.
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.
Thanks for the idea, looks like this could be a solution.
One comment I do have is by using this approach it looks like I would have to pull my source data in twice if I ever wanted to utilize other metrics (since I'm now essentially duplicating reach across the date range). I also simplified the data table a bit, theres a decent amount of other columns that I am evaluating.
If I went the measure route then I wouldn't need to do that, correct? Trying to understand if it makes more sense to pull the data in two separate times, or create a long-running measure.
Thanks again for the help!