Calculate different aggregated average depending on date in table

[KDoc]

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

[v-juanli-msft]

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]

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/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

[v-juanli-msft · Accepted solution]

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.