Calculate Average over the selected date period from Slicer

[Anonymous]

Hi Experts

Need a measure to calculate the Average between the dates as shown the the slicer selection, has to be dynamic based on the slicer slection and not fixed min and max dates.

[jennratten · Accepted solution]

Hello - 

Here is an example.  You will need to modify the table and column names to match your scenario.  If you need more assistance, please post examples of your scripts and data.

AvgSalesAmount:= 
VAR MinDate = CALCULATE ( MIN('Dates'[Date]),ALLSELECTED('Dates'[Date]) )
VAR MaxDate = CALCULATE ( MAX('Dates'[Date]),ALLSELECTED('Dates'[Date]) )
VAR Period =
    DATESBETWEEN ( 'Dates'[Date], MinDate, MaxDate )
VAR Result =
    CALCULATE (
        AVERAGEX (
            VALUES ( 'Dates'[Date] ), -- average by __ (date, month, etc)
            [Sales Amount]
        ),
        Period
    )
VAR FirstDateInPeriod = MINX ( Period, 'Dates'[Date] )
VAR LastDateWithSales = MAX ( Sales[Order Date] )
RETURN
    IF ( FirstDateInPeriod <= LastDateWithSales, Result )

[jennratten · Accepted solution]

Hello - 

Here is an example.  You will need to modify the table and column names to match your scenario.  If you need more assistance, please post examples of your scripts and data.

AvgSalesAmount:= 
VAR MinDate = CALCULATE ( MIN('Dates'[Date]),ALLSELECTED('Dates'[Date]) )
VAR MaxDate = CALCULATE ( MAX('Dates'[Date]),ALLSELECTED('Dates'[Date]) )
VAR Period =
    DATESBETWEEN ( 'Dates'[Date], MinDate, MaxDate )
VAR Result =
    CALCULATE (
        AVERAGEX (
            VALUES ( 'Dates'[Date] ), -- average by __ (date, month, etc)
            [Sales Amount]
        ),
        Period
    )
VAR FirstDateInPeriod = MINX ( Period, 'Dates'[Date] )
VAR LastDateWithSales = MAX ( Sales[Order Date] )
RETURN
    IF ( FirstDateInPeriod <= LastDateWithSales, Result )

[Anonymous]

The following code generates the following error:

MEASURE Average_Period = 
VAR MinDate = MIN(('Date'[Date]), ALLSELECTED('Date'[Date]))
VAR MaxDate = MAX(('Date'[Date]), ALLSELECTED('Date'[Date]))
VAR Period = DATESBETWEEN ( 'Date'[Date], MinDate, MaxDate )
VAR Result = 
    CALCULATE ( 
        AVERAGEX ( 
            'GasBBActualFlowStorageLast31',
            [Flow]
        ),
        Period
    )
VAR FirstDateInPeriod = MINX ( Period, 'Date'[Date] )
VAR LastDateInPeriod = MAX ( 'GasBBActualFlowStorageLast31'[GasDate] )
RETURN
    IF ( FirstDateInPeriod <= LastDateInPeriod, Result )

Error: A single value for column `Date`  in table `Date` cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

[Anonymous · Accepted solution]

This was enough to make it work:

MEASURE Avg_Selected_Period = CALCULATE(AVERAGEX('GasBBActualFlowStorageLast31',[Flow]),ALLSELECTED('GasBBActualFlowStorageLast31'[GasDate]))