Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |