How to calculate Average with DATEDIFF today plus a FILTER

[punksterz626]

Hello All,

Can someone help me formulate a DAX expression for the following criteria. I've tried and for some reason it is not showing what I want

So I have a date column and a category column

I want the AVERAGE of days between "date column" date and today with a filter from "category column"

Hope someone can help me. Thank you.

[Ashish_Mathur]

Hi,

Create a calculated column formula

Days between = today()-data[Date]

To your Table visual, drag Category and this measure

Average days between = average(data[days between])

Hope this helps.

[TheoC · Accepted solution]

Hi @punksterz626 

Create two calculated columns:

Days from Today = TODAY() - 'Table'[Start Date]

The second calculated column as follows:

Average = CALCULATE ( AVERAGEX ( 'Table' , 'Table'[Days from Today] ) , ALLEXCEPT ( 'Table' ,'Table'[Category] ) )

Output will be similar to below:

Hope this helps 🙂

Theo

[TheoC]

@punksterz626, both solutions provided by @Ashish_Mathur and myself will hopefully provide the outcome you're after. However, just to provide an explanation between primary difference between the two solutions is presented in the below table visual:

Hopefully this shows the difference between AVERAGEX and AVERAGE as well, in the power the "X" function holds.

All the best and hope the above makes sense.

Theo 🙂

[punksterz626]

Can this be done as a measure or does it have to be calculated columns? If i were to use the result with the Card visual does it matter which average i use?

[TheoC]

Hi @punksterz626 

Use Calculated columns in this instance as the syntax works. You can certainly use the Card visual. It will just give an average on the entire set of data. But you can then use the Filter pane of the visual to select what filters you want to include or exclude on your categories.