Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Measure Dax Help

    Is it possible to add a a filter in the following formula based on a calculated column? I need to exclude every number 7 in the calculation from the calendar table which it means weekends.   ...
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    hi Anonymous 

    WEEKDAY(<date>, <return_type>)

    add a parameter in the formula

    return_type A number that determines the Return value:

    Return type: 1, week begins on Sunday (1) and ends on Saturday (7). numbered 1 through 7.

    Return type: 2, week begins on Monday (1) and ends on Sunday (7).

    Return type: 3, week begins on Monday (0) and ends on Sunday (6).numbered 1 through 7.

    https://docs.microsoft.com/en-us/dax/weekday-function-dax

    Then adjust the formula as below:

    CALCULATE(COUNTROWS(FILTER(ALLSELECTED(PBI),[WEEKDAY]<6)))

     

    If you still have the problem, please share a simple sample pbix file, and your expected output.

     

    Regards,

    Lin

parry2k's avatar
parry2k
Super User
6 years ago

Anonymous do you have column for 7th day in calendar? If yes, you can simple filter it in the filter pane or in your DAX 

 

for example, you have calendar dimension with weekDay column from 1 to 7 and you can put filter in DAX to exclude 7th day.

 

Sum = 
CALCULATE ( SUM ( Table[Amount] ),
    Calendar[WeekDay] < 7
)

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi parry2k , thanks for helping me out... As you see the picture, the formula I created is to make a unique total column at the end of the matrix...in that example what the formula does is to sum the numbers and divided by the days...in this example first row we have the sum is 5 and divide by the numbers of days...in this case 18 days , so the result for the first row is 0,28 which is correct... but I need to exclude weekends and holidays.... in this case the desire result for the first row would be sum of 5 divided by 13 days (not 18 as the previous example).... in my table I have this column where it marks every saturday, sunday, and holiday with the number 7:

     

    Any suggestion in the formula?