Geomean with filter in a measure

[AliPoTD]

Hello,

Can anyone please help?

I need a meaure which calculates the GEOMEAN average of a column for all rows with a date within the last 2 months of the slicer date, but I can't get it right. Can GEOMEAN be filtered in this way?

For example, TableA

Date	Value
02/02/2024	8
03/03/2024	5
04/04/2024	6
05/05/2024	2

The user choses the date (Between) range of 1st May to 31st May from a slicer.

I want my measure to show the GEOMEAN average for all values from 2 months before, therefore from 1st April to 31st May

Date	Value
04/04/2024	6
05/05/2024	2

I thought there was a stratightforward way of doing this but can't get the syntax right.

Any help greatly appreciated! Many thanks. 

[Ashish_Mathur]

Hi,

Why can't the user simply select April 1 - May 31 in the slicer.  As simple as it gets.

[AliPoTD]

🙂

Because the user will be selecting a single month to run an invoice e.g. May 2024.

Part of that invoice includes 2 calculations:

1) to get the Geomean average of data field A for the past 3 months

2) to get the Geomean average of data field B for the past 2 months

Kind regards,

[AmiraBedh · Accepted solution]

Try this :

Measure_GEOMEAN_Last2Months = 
VAR MaxSlicerDate = MAX('TableA'[Date])
VAR MinSlicerDate = MIN('TableA'[Date])
VAR StartDate = DATE(YEAR(MinSlicerDate), MONTH(MinSlicerDate) - 2, DAY(MinSlicerDate))

RETURN
    GEOMEANX(
        FILTER(
            'TableA',
            'TableA'[Date] >= StartDate && 'TableA'[Date] <= MaxSlicerDate
        ),
        'TableA'[Value]
    )

[AliPoTD]

Hi,

Many thanks for this, it's really helpful.