Forum Discussion

joshcomputer1's avatar
joshcomputer1
Icon for Helper V rankHelper V
7 years ago
Solved

sumif with condition and date rnage

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. 

 

 

Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    7 years ago

    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

2 Replies

  • LivioLanzo's avatar
    LivioLanzo
    Icon for Solution Sage rankSolution Sage
    7 years ago

    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"
  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    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