Measure Dax Help

[Anonymous]

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:

[parry2k]

@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]

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?

[v-lili6-msft · Accepted solution]

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

[Anonymous]

Hi @v-lili6-msft 

I still cant do it... maybe the model doesnt allow it.

If you can take a look at my sample data would be so nice:

PBIX:

https://www.dropbox.com/s/7jtgn0e65ongubv/BI%20VISITAS%20DEMO%20forum.pbix?dl=0

Desire output in excel:

https://www.dropbox.com/s/r7eih1bzsw76edo/DISIRED%20OUTPUT.xlsx?dl=0