Forum Discussion

KDoc's avatar
KDoc
Frequent Visitor
7 years ago
Solved

Calculate different aggregated average depending on date in table

Im pretty new to DAX/Power BI so please bear with me ... 

 

I want to create a column that will contain a different avearge depending on the date.  I want to compare each month to an average calculated over set periods in time. These periods might overlap and they also could change. 

 

I tried creating each of the averages as variables then depending on the date (if datebeween use var1 etc but i cant get it to work and im not even sure if im on the right lines. 

 

This is how i would do it in sql 

 

with main as
(select
mth
,measure
from table_name
)

,avg as

(select
avg(case when mth between to_date('01/01/2018', 'dd/mm/yyyy') and to_date('30/06/2018','dd/mm/yyyy') then measure else 0 end) jan_june
,avg(case when mth between to_date('01/05/2018','dd/mm/yyyy') and to_date('31/12/2018','dd/mm/yyyy') then measure else 0 end) may_dec
from main)

Select mth, measure
,case when mth between to_date('01/01/2018', 'dd/mm/yyyy') and to_date('31/08/2018','dd/mm/yyyy') then jan_june else may_dec end avg
,case when mth between to_date('01/01/2018', 'dd/mm/yyyy') and to_date('31/08/2018','dd/mm/yyyy') then 'jan_june' else 'may_dec' end avg

from
main
cross join avg

for this result 

 

MTH measure AVG AVG_1
01/12/2018 5 4.87 may_dec
01/11/2018 4 4.87 may_dec
01/10/2018 3 4.87 may_dec
01/09/2018 3 4.87 may_dec
01/08/2018 4 6.16 jan_june
01/07/2018 6 6.16 jan_june
01/06/2018 7 6.16 jan_june
01/05/2018 7 6.16 jan_june
01/04/2018 8 6.16 jan_june
01/03/2018 5 6.16 jan_june
01/02/2018 5 6.16 jan_june
01/01/2018 5 6.16 jan_june

 

  • Hi KDoc 

    Create calculated columns

    year = YEAR([date])
    
    month = MONTH([date])

    Create measures

    cate =
    SWITCH (
        TRUE (),
        MAX ( Sheet10[year] ) = 2018
            && MAX ( Sheet10[month] ) >= 1
            && MAX ( Sheet10[month] ) <= 8, "jan_june",
        MAX ( Sheet10[year] ) = 2018
            && MAX ( Sheet10[month] ) >= 9
            && MAX ( Sheet10[month] ) <= 12, "may_dec"
    )
    
    avg =
    VAR avg1 =
        SUMX (
            FILTER (
                ALL ( Sheet10 ),
                Sheet10[year] = 2018
                    && Sheet10[month] >= 1
                    && Sheet10[month] <= 6
            ),
            [Measure]
        ) / 6
    VAR avg2 =
        SUMX (
            FILTER (
                ALL ( Sheet10 ),
                Sheet10[year] = 2018
                    && Sheet10[month] >= 5
                    && Sheet10[month] <= 12
            ),
            [Measure]
        ) / 8
    RETURN
        SWITCH ( [cate], "jan_june", avg1, "may_dec", avg2 )
    

     

    Best Regards

    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support

    Hi KDoc 

    Could you show an example data before transforming?

    If I have some original data and results based on the data, then i may find how to do it in Power BI.

     

    Best Regards

    Maggie

    • KDoc's avatar
      KDoc
      Frequent Visitor

      Hi Maggie, 

      So im starting with my monnth and result, then the avg is the calculated column.

      Where the month is from jan - oct i want ti calculate the average between jan - june, and when the mth between sept dec i want to calculate the average may - dec results. Thanks.

       

      MTH

      Measure Result

      AVG

      01/12/201854.87may_dec
      01/11/201844.87may_dec
      01/10/201834.87may_dec
      01/09/201834.87may_dec
      01/08/201846.16jan_june
      01/07/201866.16jan_june
      01/06/201876.16jan_june
      01/05/201876.16jan_june
      01/04/201886.16jan_june
      01/03/201856.16jan_june
      01/02/201856.16jan_june
      01/01/201856.16jan_june
      • v-juanli-msft's avatar
        v-juanli-msft
        Icon for Community Support rankCommunity Support

        Hi KDoc 

        Create calculated columns

        year = YEAR([date])
        
        month = MONTH([date])

        Create measures

        cate =
        SWITCH (
            TRUE (),
            MAX ( Sheet10[year] ) = 2018
                && MAX ( Sheet10[month] ) >= 1
                && MAX ( Sheet10[month] ) <= 8, "jan_june",
            MAX ( Sheet10[year] ) = 2018
                && MAX ( Sheet10[month] ) >= 9
                && MAX ( Sheet10[month] ) <= 12, "may_dec"
        )
        
        avg =
        VAR avg1 =
            SUMX (
                FILTER (
                    ALL ( Sheet10 ),
                    Sheet10[year] = 2018
                        && Sheet10[month] >= 1
                        && Sheet10[month] <= 6
                ),
                [Measure]
            ) / 6
        VAR avg2 =
            SUMX (
                FILTER (
                    ALL ( Sheet10 ),
                    Sheet10[year] = 2018
                        && Sheet10[month] >= 5
                        && Sheet10[month] <= 12
                ),
                [Measure]
            ) / 8
        RETURN
            SWITCH ( [cate], "jan_june", avg1, "may_dec", avg2 )
        

         

        Best Regards

        Maggie

         

        Community Support Team _ Maggie Li
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.