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.