Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I have 7 columns representing days of the week (1 to 7, Sunday to Monday)
What I need to do is sum the columns based on todays day number.
For Example. Today is Wednesday (Day 4) I want to Sum columns 4, 5, 6 and 7 but tomorrow I want it to sum columns 5, 6 and 7. And on Monday for instance it would need to sum columns 2, 3, 4, 5, 6 and 7
How can i write something that will only sum the data in my columns based on the Day Number?
In Excel I had it as
=SUMIF($F$1:$L$1,">"&WEEKDAY(TODAY()),F3:L3)
Where Row 1 would be my headers (1 to 7) and Row 3 will be the data I want to sum
SOmehow need to replicate this is Power BI
Many Thanks
Simon
Solved! Go to Solution.
Hi @SiGill1979 ,
For your scenario, we need to unpivot the columns firstly in Query Editor. I created a sample that you can have a try.
In Query Editor:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VZFRDsAgCEPvwveyiCDqWYz3v8bWGpe5j0bxUUo2hmiWS3J6ResrRimoOWou8xqSFQWQ2nHCuwVOdQkgS7uLOK+efidC7GerUe5YH4wcEwz1YqS9gWl7ZE63/mi/vxwR5Evd4xhM43DnI+bXTjrQadwfVbXDvdOdkXomXzkOCxoW7NucfAHoIDQV4s13Oq4U233hKzwXVG1s6MCsfT/g9Kcoc6rM+QA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"1" = _t, #"2" = _t, #"3" = _t, #"4" = _t, #"5" = _t, #"6" = _t, #"7" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"1", Int64.Type}, {"2", Int64.Type}, {"3", Int64.Type}, {"4", type number}, {"5", type number}, {"6", Int64.Type}, {"7", Int64.Type}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value") in #"Unpivoted Columns"
In report view:
I created a calendar table.
Table 2 = CALENDAR(DATE(2019,9,25),DATE(2019,10,10)) WEEKDAY = WEEKDAY('Table 2'[Date],2) Measure = CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Attribute] > WEEKDAY(TODAY(),2) ))
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @SiGill1979 ,
For your scenario, we need to unpivot the columns firstly in Query Editor. I created a sample that you can have a try.
In Query Editor:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VZFRDsAgCEPvwveyiCDqWYz3v8bWGpe5j0bxUUo2hmiWS3J6ResrRimoOWou8xqSFQWQ2nHCuwVOdQkgS7uLOK+efidC7GerUe5YH4wcEwz1YqS9gWl7ZE63/mi/vxwR5Evd4xhM43DnI+bXTjrQadwfVbXDvdOdkXomXzkOCxoW7NucfAHoIDQV4s13Oq4U233hKzwXVG1s6MCsfT/g9Kcoc6rM+QA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"1" = _t, #"2" = _t, #"3" = _t, #"4" = _t, #"5" = _t, #"6" = _t, #"7" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"1", Int64.Type}, {"2", Int64.Type}, {"3", Int64.Type}, {"4", type number}, {"5", type number}, {"6", Int64.Type}, {"7", Int64.Type}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value") in #"Unpivoted Columns"
In report view:
I created a calendar table.
Table 2 = CALENDAR(DATE(2019,9,25),DATE(2019,10,10)) WEEKDAY = WEEKDAY('Table 2'[Date],2) Measure = CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Attribute] > WEEKDAY(TODAY(),2) ))
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
Check out the May 2024 Power BI update to learn about new features.
User | Count |
---|---|
90 | |
80 | |
60 | |
59 | |
58 |
User | Count |
---|---|
157 | |
119 | |
104 | |
77 | |
69 |