Search code examples
exceldaxpowerquerypowerpivot

Measure to sum values ​for weekdays only

I get a table in PowerPivot (Excel) via Microsoft Query in the following structure: Value - Date - DOC - ID

So I get several daily entries for the same ID, like this:

200 - 01.02.2019 - 0011 - AAI

250 - 01.02.2019 - 0012 - AAI

20 - 01.02.2019 - 0013 - AAI

290 - 01.02.2019 - 0014 - AAI

280 - 02.02.2019 - 0015 - AAII

290 - 02.02.2019 - 0016 - AAII

In the end, I aggregate the values ​​per day, so: Date - Sum

Now the goal is to assign all values ​​assigned to a Saturday or Sunday to Friday. How is this possible? Using a measure?

Solution

  • I'm assuming your dates start in cells A1

    Make a column using WEEKDAY , IF & OR:

    =if(or(weekday(A1,2)=6,weekday(A1,2)=7),5,weekday(A1,2))

    This will give you the information for each day, but assigning Saturday and Sunday to Friday instead. When you aggregate your information it will be together by the day number, with Monday being 1 and so on.