AllExcept DAX help needed

[robarbie]

Hi,

I have several existing DAX measure that needs to be changed.  One of them is shown below.

These are the instructions  I received.

FOR the QTD ADC and YTD ADC I works off the max date in the slicer, but only shows data for values within the range. I believe it will need to have the QTD and YTD sum calculated using an ALLEXCEPT function to get rid of the date filters. 

The days in quarter and days in year are working correctly. All we need is the sum using the ALLEXCEPT and then dividing it by the appropriate denominator (daysInQtr, or Days in year)

QTD ADC =

VAR __maxDateRange = MAX('Filtered Dim Date'[Date])

VAR __qtrStart = LOOKUPVALUE('Filtered Dim Date'[Start of Quarter], 'Filtered Dim Date'[Date], __maxDateRange)

VAR __daysInQtr = DATEDIFF(__maxDateRange, __qtrStart, DAY)

VAR __totalSum = CALCULATE(SUM('Census by Payer Consolidated Campus'[Daily Census]), ALLEXCEPT('Census by Payer Consolidated Campus','Census by Payer Consolidated Campus'[Census Payer]))

RETURN

CALCULATE(DIVIDE(

SUM('Census by Payer Consolidated Campus'[Daily Census]),

[daysInQtr]

), ALL('Census by Payer Consolidated Campus'[Daily Census]), QUARTER('Census by Payer Consolidated Campus'[Census Date]) = QUARTER(__maxDateRange))

I have no clue as what he wants or how to accomplish this task.

Can anyone help?

Thanks,

Rochelle

[v-stephen-msft]

Hi @robarbie ,

Can you provide some dummy data and show the desired result?

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[daxer-almighty]

First, this is all about time-intelligence: https://www.youtube.com/watch?v=FxiAYGbCfAQ

Second, ALLEXCEPT is a very treacherous function that most people do not understand. You should first learn a bit about it before you decide to use it: https://www.youtube.com/watch?v=ubrv53XaiFI