Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Running average with months without any values

Hi everyone,

 

I have hit a brick wall when trying to calculate a running average in one year for which some months have no values, in my example no sales (no RESSALE[SELLINGPRICE]).

 

This is my measure:

DIVIDE (
CALCULATE (
SUM ( RESSALE[SELLINGPRICE]);
FILTER (
ALLSELECTED ( 'Calendar' );
'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
)
);
CALCULATE (
DISTINCTCOUNT ( 'Calendar'[Date].[Maand]);
FILTER (
ALLSELECTED( 'Calendar');
'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
)
);
0
)

 

This is the result:

So actually the results are good. The total is 945.000 over the whole year. So that number divided by 11 is 86.727. All the months that had sales show up and are calculated correctly.

But, i want to show all months with all running average values.

 

The calendar[date] is a proper calendar table, which has a relationship to the datamodel. I have tried a lot of things, but i never can get all the months to show...

 

Apologies in advance: i cannot share my pbix file.

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    Hi everyone,

     

    So, i couldnt let this one go and popped up my notebook in the evening to try some more. I quickly found out i was going to wrong route with the allselected feature, and also was heading the wrong way with the denominator.

     

    I eventually achieve the desired result with the following measure:

     

    SELLINGPRICE YTD =
    IF (
        ISFILTERED ( 'Calendar'[Date] );
        ERROR ( "Does not compute." );
        TOTALYTD (
            SUMX (
                RESSALE;
                'RESSALE'[SELLINGPRICE] / DISTINCTCOUNT ( 'Calendar'[Date].[Maand] )
            );
            'Calendar'[Date].[Date]
        )
    )

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi everyone,

     

    So, i couldnt let this one go and popped up my notebook in the evening to try some more. I quickly found out i was going to wrong route with the allselected feature, and also was heading the wrong way with the denominator.

     

    I eventually achieve the desired result with the following measure:

     

    SELLINGPRICE YTD =
    IF (
        ISFILTERED ( 'Calendar'[Date] );
        ERROR ( "Does not compute." );
        TOTALYTD (
            SUMX (
                RESSALE;
                'RESSALE'[SELLINGPRICE] / DISTINCTCOUNT ( 'Calendar'[Date].[Maand] )
            );
            'Calendar'[Date].[Date]
        )
    )