Forum Discussion

SiGill1979's avatar
SiGill1979
Frequent Visitor
6 years ago
Solved

Sum certain columns based on todays date

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

Show and Tell
  • v-xuding-msft's avatar
    v-xuding-msft
    6 years ago

    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.

1 Reply

  • v-xuding-msft's avatar
    v-xuding-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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.