Forum Discussion
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
Community 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
- KDocFrequent 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/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
Community 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.