Forum Discussion

Pedro77000's avatar
Pedro77000
Frequent Visitor
6 years ago
Solved

Calculate an average with date filter

Hi Everyone,   I have a problem of averaging according to a context. I have a data model with a fact table and a "Date" dimension table Indeed, I want to calculate the average based on the mont...
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    6 years ago

    Hi Pedro77000 ,

     

    We can create a measure as below.

    Measure =
VAR SUMA =
    CALCULATE (
        SUM ( 'Table'[value] ),
        FILTER ( ALL ( 'Table' ), 'Table'[date] <= MAX ( 'date'[Date] ) )
    )
VAR COUNTM =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[YearMOnth] ),
        FILTER ( ALL ( 'Table' ), 'Table'[date] <= MAX ( 'date'[Date] ) )
    )
RETURN
    DIVIDE ( SUMA, COUNTM )

     

    Also you can find the pbix as attached.

     

  • v-frfei-msft's avatar
    v-frfei-msft
    6 years ago

    Hi Pedro77000 ,

     

    To use ALLEXCEPT instead of ALL should work.

     

    FILTER ( ALLEXCEPT ( 'Table','Table'[region] ), 'Table'[date] <= MAX ( 'date'[Date] ) )

     

    If it doesn't meet your requirement,  Kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

     

v-frfei-msft's avatar
v-frfei-msft
Community Support
6 years ago

Hi Pedro77000 ,

 

We can create a measure as below.

Measure =
VAR SUMA =
    CALCULATE (
        SUM ( 'Table'[value] ),
        FILTER ( ALL ( 'Table' ), 'Table'[date] <= MAX ( 'date'[Date] ) )
    )
VAR COUNTM =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[YearMOnth] ),
        FILTER ( ALL ( 'Table' ), 'Table'[date] <= MAX ( 'date'[Date] ) )
    )
RETURN
    DIVIDE ( SUMA, COUNTM )

 

Also you can find the pbix as attached.

 

  • Pedro77000's avatar
    Pedro77000
    Frequent Visitor
    6 years ago

    Good morning v-frfei-msft !!

     

    Thank you very much for this reply.

    The solution works well. But this average I cannot have it by region, I believe because of the filter ALL.

    The average for the total is good but when I split by region, it's the same overall average that appears in front of each region.

     

    • v-frfei-msft's avatar
      v-frfei-msft
      Community Support
      6 years ago

      Hi Pedro77000 ,

       

      To use ALLEXCEPT instead of ALL should work.

       

      FILTER ( ALLEXCEPT ( 'Table','Table'[region] ), 'Table'[date] <= MAX ( 'date'[Date] ) )

       

      If it doesn't meet your requirement,  Kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

       

      • Pedro77000's avatar
        Pedro77000
        Frequent Visitor
        6 years ago

        Thank you so much Franck ! that works well ! even if the disadvantage of ALLEXCEPT is having to enter the fields of all dimension tables.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Pedro77000 

       

      Try changing ALL to ALLSELECTED and then it should carry your filters through.

       

      ---
      Please hit the "Accept as Solution" button if my post answered your question! If my post was helpful please consider giving it a "Thumbs Up."