locked
Sum certain columns based on formula RRS feed

  • 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 SiGill1979 Wednesday, October 2, 2019 10:13 AM
    Wednesday, October 2, 2019 10:11 AM

Answers

  • 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 anthony34 Wednesday, October 2, 2019 11:27 AM
    Wednesday, October 2, 2019 11:23 AM
  • I am replying to your deleted message.

    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 anthony34 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