Sum certain columns based on formula

• Question

• 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

• Edited by Wednesday, October 2, 2019 10:13 AM
Wednesday, October 2, 2019 10:11 AM

• and if what you want is the total of all the rows matching your day criteria, namely 1 single number then you can try:

```let
Source = Excel.CurrentWorkbook(){[Name="tbl_sum_if"]}[Content],
today_day = Date.DayOfWeek(DateTime.FixedLocalNow(), Day.Sunday)
in List.Sum(Table.AddColumn(Source, "total", each List.Sum(List.Skip(Record.ToList(_), today_day)) ) [total] )```

Wednesday, October 2, 2019 11:50 AM

All replies

• I am assuming your source table has only 1 row, like:

then you can use this code:

```let
Source = Excel.CurrentWorkbook(){[Name="tbl_sum_if"]}[Content],
today_day = Date.DayOfWeek(DateTime.FixedLocalNow(), Day.Sunday)
in List.Sum(List.Skip(Table.Transpose(Source) [Column1], today_day))```

• Edited by Wednesday, October 2, 2019 11:27 AM
Wednesday, October 2, 2019 11:23 AM

If you have several rows, and want to add a new column with the specific total of each row, you can use :

```let
Source = Excel.CurrentWorkbook(){[Name="tbl_sum_if"]}[Content],
today_day = Date.DayOfWeek(DateTime.FixedLocalNow(), Day.Sunday)
in Table.AddColumn(Source, "total", each List.Sum(List.Skip(Record.ToList(_), today_day)) )```

• Edited by Wednesday, October 2, 2019 11:43 AM
Wednesday, October 2, 2019 11:37 AM
• and if what you want is the total of all the rows matching your day criteria, namely 1 single number then you can try:

```let
Source = Excel.CurrentWorkbook(){[Name="tbl_sum_if"]}[Content],
today_day = Date.DayOfWeek(DateTime.FixedLocalNow(), Day.Sunday)
in List.Sum(Table.AddColumn(Source, "total", each List.Sum(List.Skip(Record.ToList(_), today_day)) ) [total] )```

Wednesday, October 2, 2019 11:50 AM