cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper V

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.

1 ACCEPTED SOLUTION
Employee

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

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Employee

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

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Solution Sage

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),
ChangedType2 = Table.TransformColumnTypes(ExpandedCustom,{{"Custom", type date}}),
#"Removed Other Columns" = Table.SelectColumns(ChangedType2,{"Custom", "open_tasks", "deviance"})
in
#"Removed Other Columns"```

Proud to be a Datanaut!

Announcements

Fabric certifications survey

Certification feedback opportunity for the community.

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors