Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
Solved! Go to 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
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
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"
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
103 | |
99 | |
98 | |
38 | |
37 |
User | Count |
---|---|
151 | |
121 | |
73 | |
71 | |
63 |