I need some help getting the average for an existing measure

[RichOB]

Hi, I need to get the average Days_Open between 01/04/2024 and 30/07/2024. My calculation gives me the Days_Total of 44, but how can I then turn that into an average please?

 Date_Range =

    CALCULATE(

        SUM('Table'[Days_Open]),

        'Table' [Start_Date]>=DATE(2024,4,1),

        'Table' [Start_Date]<=DATE(2024,7,30)

        )

    )

Location	Kennel	Start_Date	End_Date	Days_Open
Manchester	1	01/04/2024	10/04/2024	10
Manchester	1	01/06/2024	14/06/2024	14
Manchester	1	01/07/2024	20/07/2024	20
Manchester	1	01/08/2024	20/08/2024	20

[dharmendars007 · Accepted solution]

Hello @RichOB , 

To calculate the average Days_Open within your date range (01/04/2024 to 30/07/2024), you can modify your DAX measure by dividing the total Days_Open by the number of rows that fall within the specified range.

Average_Days_Open =
DIVIDE(
CALCULATE(
SUM('Table'[Days_Open]),
'Table'[Start_Date] >= DATE(2024, 4, 1),
'Table'[Start_Date] <= DATE(2024, 7, 30)),
CALCULATE(
COUNTROWS('Table'),
'Table'[Start_Date] >= DATE(2024, 4, 1),
'Table'[Start_Date] <= DATE(2024, 7, 30)))

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes 👍 are much appreciated!

Thank You

Dharmendar S

LinkedIN 

[Anonymous · Accepted solution]

Hi @RichOB 

Thanks for the reply from dharmendars007 .

You can also try the following measure:

Date_Range = 
    CALCULATE(
        AVERAGE('Table'[Days_Open]),
        'Table' [Start_Date] >= DATE(2024,4,1),
        'Table' [Start_Date] <= DATE(2024,7,30)
        )

Output:

Best Regards,
Yulia Xu

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

[dharmendars007 · Accepted solution]

Hello @RichOB , 

To calculate the average Days_Open within your date range (01/04/2024 to 30/07/2024), you can modify your DAX measure by dividing the total Days_Open by the number of rows that fall within the specified range.

Average_Days_Open =
DIVIDE(
CALCULATE(
SUM('Table'[Days_Open]),
'Table'[Start_Date] >= DATE(2024, 4, 1),
'Table'[Start_Date] <= DATE(2024, 7, 30)),
CALCULATE(
COUNTROWS('Table'),
'Table'[Start_Date] >= DATE(2024, 4, 1),
'Table'[Start_Date] <= DATE(2024, 7, 30)))

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes 👍 are much appreciated!

Thank You

Dharmendar S

LinkedIN