Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to 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.
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
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 |
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.
User | Count |
---|---|
77 | |
75 | |
67 | |
65 | |
47 |
User | Count |
---|---|
108 | |
106 | |
93 | |
84 | |
66 |