sumif with condition and date rnage

[joshcomputer1]

I have a dataset that I am trying to sum the [deviance] column so that it is a rolling total by day.  I need to filter by another column [open_tasks] so that only "open" tasks are included and that the deviance is only calculated if the date selected in the slicer is within the start_date to end_date range.  *I have a separate calendar table named [date].  Please look at the bottom chart for the desired result. 

[v-yulgu-msft · Accepted solution]

Hi @joshcomputer1 ,

Please refer to below measure.

Devaince Measure =
CALCULATE (
    SUM ( Dataset[deviance] ),
    FILTER (
        Dataset,
        Dataset[start_date] <= MAX ( 'Date'[Date] )
            && Dataset[end_date] >= MAX ( 'Date'[Date] )
            && Dataset[open_tasks] = "open"
    )
)

Best regards,

Yuliana Gu

[LivioLanzo]

Hello @joshcomputer1 

the trick here is to apply the below transformation within Power Query and then everything will become super easy:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQHQkslHSVjfWMDCMvQAEjkF6TmKcXqgBQYQ4SN9I1MoQpMgURyTn5xagpUiSnMDCNzqBJUI8zh8hYQlimqvCVUl74x1DWWSDbEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [start_date = _t, end_date = _t, deviance = _t, open_tasks = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"start_date", type date}, {"end_date", type date}, {"deviance", Int64.Type}, {"open_tasks", type text}}),
    AddedCustom = Table.AddColumn(ChangedType, "Custom", each List.Dates([start_date], Duration.Days([end_date] - [start_date]) + 1, #duration(1,0,0,0)), type list),
    ExpandedCustom = Table.ExpandListColumn(AddedCustom, "Custom"),
    ChangedType2 = Table.TransformColumnTypes(ExpandedCustom,{{"Custom", type date}}),
    #"Removed Other Columns" = Table.SelectColumns(ChangedType2,{"Custom", "open_tasks", "deviance"})
in
    #"Removed Other Columns"