Forum Discussion
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.
Avg Total 2 =
VAR Months =
COUNTROWS ( ALLSELECTED ( PBI[Fecha] ) )
RETURN
IF (
ISINSCOPE ( PBI[Fecha] );
SUM ( PBI[Total] );
DIVIDE ( SUM ( PBI[Total] ); Months )
)this formula shows the folowing result:
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
4 Replies
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 )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?
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
