cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nicksonteh
Helper III
Helper III

DAX Help : Previous 3 Months average

​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

 

nicksonteh_0-1655372567489.png

 

Any help is very much appreciated. Thank you in advance.

2 ACCEPTED SOLUTIONS
SpartaBI
Community Champion
Community Champion

@nicksonteh 

 

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
    )

 

 


2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png

Showcase Report – Contoso By SpartaBI


SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Full-Logo11.png

View solution in original post

SpartaBI
Community Champion
Community Champion

@nicksonteh 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:

Average of Previous 3 Full Months Measure Dependant on Coloumn =
AVERAGE('Table '[Average of Previous 3 Full Months Calculated Column])


2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png

Showcase Report – Contoso By SpartaBI


SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Full-Logo11.png

View solution in original post

10 REPLIES 10
SpartaBI
Community Champion
Community Champion

@nicksonteh 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. 

SpartaBI
Community Champion
Community Champion

@nicksonteh 

 

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
    )

 

 


2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png

Showcase Report – Contoso By SpartaBI


SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Full-Logo11.png

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

nicksonteh_0-1655437649658.png

nicksonteh_3-1655437839970.png

 

 

SpartaBI
Community Champion
Community Champion

@nicksonteh 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:

Average of Previous 3 Full Months Measure Dependant on Coloumn =
AVERAGE('Table '[Average of Previous 3 Full Months Calculated Column])


2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png

Showcase Report – Contoso By SpartaBI


SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Full-Logo11.png

Hi @SpartaBI , thanks for the clarification !!! appreciate!

SpartaBI
Community Champion
Community Champion

@nicksonteh 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 ?

SpartaBI
Community Champion
Community Champion

@nicksonteh 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 !

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors