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 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.
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 |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |