Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

@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
    )

 

 


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

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

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

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

Anonymous
Not applicable

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
    )

 

 


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

Anonymous
Not applicable

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

 

 

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

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

Anonymous
Not applicable

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

@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 🙂

Anonymous
Not applicable

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

Anonymous
Not applicable

yup ! have mark that as solution already !

will definetely check on the report !

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors