Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Filter with aggregate functions

Hey everybody,

 

I'm trying to calculate the moving average for some data I have at hand.

 

Everything works fine, when I apply the AVERAGE function like this (see last column):

TRIC Total Avg = 
AVERAGE(KPIs[TRIC])

 

But as soon as I try to force a DATESINPERIOD filter Power BI just copies the values from the referenced column (see 2nd to last column). This is the DAX function I used:

TRIC 1 Year Avg = 
CALCULATE(
    AVERAGE(KPIs[TRIC]);
    DATESINPERIOD(
        KPIs[Incident Date].[Date];
        LASTDATE(KPIs[Incident Date].[Date]);
        -1;
        YEAR
    )
)

 

The same problem occurs for other functions such as AVERAGEX or SUM in columns and in measures.

Does anybody know a solution to this?

Thanks in advance,

NikRob

PS: Don't get irritated by the ";" as they are used instead of "," in the German implementation of Power BI ;-)

Need Help

4 Replies

  • parry2k's avatar
    parry2k
    Super User
    7 years ago

    Anonymous are you adding measures or columns? It should be measure.

    • parry2k's avatar
      parry2k
      Super User
      7 years ago

      Anonymous also it make sense to add a date dimension in your model to make time intelligence calculations. there are many posts on how to create date/calendar dimension

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        parry2k As I said, I tried both column and measure. I know this is better placed in a measure, but for the screenshot I took, I figured it would be more convenient to have it as a column.

        I am new to Power BI, so maybe you can help me a little bit more. I did create a new calendar table with all the dates in question (01-01-2017 to 12-31-2019). I do however not see how this helps with the averaging and filtering problems I have.