Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all, hope you are doing well
I'm trying to create a measure to get Previous 3 month average, yet could not figure out on how to do this in DAX. For example, based on the the picture attahced below:
-Jan can be blank
-Febuary can be blank
-March can be blank
-April onwards will sum every number from January to March and devide by 3, because we are looking at the average of previous 3 month.
-May will sum every number from February to April and devide by 3
Any help is very much appreciated. Thank you in advance.
Solved! Go to Solution.
@Anonymous
Average of Previous 3 Full Months =
VAR _current_date = 'Table'[GRN Date]
VAR _end_point = EOMONTH(_current_date, -1)
VAR _start_point = EOMONTH(_current_date, -4) + 1
VAR _min_date_total = MIN('Table'[GRN Date])
VAR _result =
DIVIDE(
SUMX(
FILTER(
'Table',
'Table'[GRN Date] >= _start_point && 'Table'[GRN Date] <= _end_point
),
'Table'[Lead Time (Days)]
),
3
)
RETURN
IF(
EOMONTH(_start_point, -1) >= EOMONTH(_min_date_total, -1),
_result
)
@Anonymous we are not dealing here with best practice stuff, but, as we created the column before you can create this measure to use as the line value:
@Anonymous in your photo is that a data table and you need a calculated column or that is a visual table and you want to add a measure? If it's a visual what is the measure you have there for Lead Time (Days)?
is actually a table that need to calculated a column ya.
@Anonymous
Average of Previous 3 Full Months =
VAR _current_date = 'Table'[GRN Date]
VAR _end_point = EOMONTH(_current_date, -1)
VAR _start_point = EOMONTH(_current_date, -4) + 1
VAR _min_date_total = MIN('Table'[GRN Date])
VAR _result =
DIVIDE(
SUMX(
FILTER(
'Table',
'Table'[GRN Date] >= _start_point && 'Table'[GRN Date] <= _end_point
),
'Table'[Lead Time (Days)]
),
3
)
RETURN
IF(
EOMONTH(_start_point, -1) >= EOMONTH(_min_date_total, -1),
_result
)
Hi @SpartaBI , sincerrely apologise to interrupt you again.
I have face another difficulties that is similar to this question. However, in the current case, I would like to created visual table and add a measure. For example, based on the visual below, the light blue color bar can be refer to the average of the lead times (which i can just drag the column into the visual and click calculate average); the main problem that I face, is that how am i able to show the average lead time of the previous 3 month in the line value, for instance, the line chart of april should show 19 ,may is 22 , june is 15. Thanks you so much for your assistance
@Anonymous we are not dealing here with best practice stuff, but, as we created the column before you can create this measure to use as the line value:
@Anonymous my pleasure. Please also mark the previous message as a solution for community visabilty.
P.S.
Check out my showcase report - got some high level stuff there 🙂
https://community.powerbi.com/t5/Data-Stories-Gallery/SpartaBI-Feat-Contoso-100K/td-p/2449543
Give it a thumbs up if you liked it 🙂
@SpartaBI , do you have any experience on automating the process of extrating data from different data source ? such as Oracle ?
@Anonymous not sure what you meant?
1. Extracting data from oracle to Power BI? (In this case you need an oracle client and a Power BI gateway)
2. Creating an automation process that will create a DW from the data in Oracle in something that is more suitable for Power BI like Azure SQL?
yup ! have mark that as solution already !
will definetely check on the report !
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
39 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
44 | |
32 | |
30 | |
18 | |
17 |