cancel
Showing results for
Did you mean:

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.

Helper I

## Calculate average of last 3 occurrences of category between 15 days date range

Hi All,

My actual requirement is I need to calculate UCL and LCL for the below data. But before that I need to calculate average for which I need your help.

 date group sales 1/1/2023 A 10 1/2/2023 A 1/3/2023 A 1/4/2023 A 20 1/5/2023 A 30 1/5/2023 A 30 1/6/2023 A 1/7/2023 A 10 1/7/2023 A 10 1/9/2023 B 10 1/9/2023 B 20 1/10/2023 B 1/11/2023 B 60 1/12/2023 B 10 1/12/2023 B 30 1/13/2023 C 1/14/2023 C 10 1/15/2023 C 10 1/16/2023 C 10

If you select 15th Jan as Min(Date) and 16th Jan as Max(Date) in Date slicer then the date range is from 1st Jan to 15th Jan which is 15 days.

Within this 15 days range, for each group category, of its last 3 occurrances(If there are 2 or more records on same day that need to be considered for avg calculation) average needs to be calculated.

For eg:

 Category Last 3 occurrances date sales value A 7th Jan, 5th Jan, 4th Jan Avg(10,10,30,30,20) B 12th Jan ,11th Jan, 9th Jan Avg(30,10,60,20,10) C there are no 3 occurrances 0

Thanks,

1 ACCEPTED SOLUTION
Super User

I think this will give you what you are looking for.

``Sales Amount = SUM ( 'YourTable'[sales] )``
``````Measure =
VAR _Dates =
TOPN (
3,
FILTER ( VALUES ( 'YourTable'[date] ), NOT ISBLANK ( [Sales Amount] ) ),
'YourTable'[date], DESC
)
RETURN
IF (
COUNTROWS ( _Dates ) < 3,
BLANK (),
CALCULATE ( AVERAGE ( 'YourTable'[sales] ), _Dates )
)``````

6 REPLIES 6
Super User

In your description you have "Date range - 5th April to 10th April in the date slicer" but your sample data does not have any data in that date range.

Helper I

Sorry, I was ill for few days Hence could not reply.

I was able to calculate the average with the help of your query. Added a filter condition in the beginning which gave me the desired result what I was looking for.

Thank you very much!!.

I will accept your solution.

average =
var slicerdate= FILTER(values(diameter_ucl_lcl_data[date_range]),diameter_ucl_lcl_data[date_range]<min('date_master'[Date]))

VAR _Dates =
TOPN (
3,
FILTER ( slicerdate, NOT ISBLANK ( [diameter] )),
diameter_ucl_lcl_data[date_range], DESC
)
RETURN
IF (
COUNTROWS ( _Dates ) < 3,
0,
CALCULATE ( AVERAGE ( diameter_ucl_lcl_data[f_val] ), _Dates )
)

Thanks,

Super User

Sorry I don't understand what you mean by

"see the average of diameter for date range 5th April to 10th April"

If you want to see the the average for those days, why are we looking at 22nd March to 5th April?

What range are they selecting in the date slicer?

Helper I

Happy New Year!!

Sorry for the inconvenience. Let me explain you the requirement again.

Client wants to see the list of diameter values for below slicer selections. But UCL, LCL values need to be calculated for the last 3 occurences  of diameter values from "from date" i.e 5th April ( it can be 4th, 2nd and 1st April) for the same hose socket crimp type and measurement description selections.

Date range - 5th April to 10th April in the date slicer

hose socket crimp type - 171-10-1172/13512-T

measurement description - Hose End Crimp Diameter  and  Connection End Crimp Diameter

I know requirement looks strange. But client wants chart to be displayed in this way.

My UI team is displaying the same chart using angular. I need to show it in PowerBI.

Please let me know If you still have questions.

Thanks,

Helper I

Thanks for the quick response.

I have got the actual requirement from client .

Requirement is almost similar to the above one.

 workstation job_number hose_socket_crimp_type measurement_description date diameter SFX02RUFIX036 H0376793 171-10-1172/13512-T Hose End Crimp Diameter 3/27/2023 0.575 SFX02RUFIX036 H0376793 171-10-1172/13512-T Hose End Crimp Diameter 3/27/2023 0.575 SFX02RUFIX036 H0376793 171-10-1172/13512-T Hose End Crimp Diameter 3/27/2023 0.575 SFX02RUFIX036 H0378380 171-10-1172/13512-T Hose End Crimp Diameter 3/27/2023 0.575 SFX02RUFIX036 H0378380 171-10-1172/13512-T Hose End Crimp Diameter 3/27/2023 0.575 SFX02RUFIX036 H0378380 171-10-1172/13512-T Hose End Crimp Diameter 3/27/2023 0.575 SFX02RUFIX036 H0378378 171-10-1172/13512-T Connection End Crimp Diameter 4/3/2023 1.5765 SFX02RUFIX036 H0378378 171-10-1172/13512-T Connection End Crimp Diameter 4/3/2023 1.5765 SFX02RUFIX036 H0378378 171-10-1172/13512-T Connection End Crimp Diameter 4/3/2023 1.5765 SFX02RUFIX036 H0378378 171-10-1172/13512-T Hose End Crimp Diameter 4/3/2023 0.575 SFX02RUFIX036 H0378378 171-10-1172/13512-T Hose End Crimp Diameter 4/3/2023 0.575 SFX02RUFIX036 H0378378 171-10-1172/13512-T Hose End Crimp Diameter 4/3/2023 0.575 SFX02RUFIX036 H0377096 171-10-1172/13512-T Connection End Crimp Diameter 4/4/2023 1.5765 SFX02RUFIX036 H0377096 171-10-1172/13512-T Connection End Crimp Diameter 4/4/2023 1.5765 SFX02RUFIX036 H0377096 171-10-1172/13512-T Connection End Crimp Diameter 4/4/2023 1.5765 SFX02RUFIX036 H0377096 171-10-1172/13512-T Hose End Crimp Diameter 4/4/2023 0.575 SFX02RUFIX036 H0377096 171-10-1172/13512-T Hose End Crimp Diameter 4/4/2023 0.575 SFX02RUFIX036 H0377096 171-10-1172/13512-T Hose End Crimp Diameter 4/4/2023 0.575

Above table is the sample data from db.

There are 3 slicers.

date - range selection

hose socket crimp type- single selection

measurement description - multiple selection

Requirement:   If client wants to see the average of diameter for date range 5th April to 10th April, for hose socket crimp type 171-10-1172/13512-T and measurement description as Hose End Crimp Diameter , Connection End Crimp Diameter, then we need to check data of previous 15 days from 5th April. i.e 22nd March to 5th April.

In this date range, need to check the last 3 occurrances of hose socket crimp type and measurement description combination and take the average for all diameter values.

How can we achieve this by modifying the Dax which you provided.

Thanks,

Super User

I think this will give you what you are looking for.

``Sales Amount = SUM ( 'YourTable'[sales] )``
``````Measure =
VAR _Dates =
TOPN (
3,
FILTER ( VALUES ( 'YourTable'[date] ), NOT ISBLANK ( [Sales Amount] ) ),
'YourTable'[date], DESC
)
RETURN
IF (
COUNTROWS ( _Dates ) < 3,
BLANK (),
CALCULATE ( AVERAGE ( 'YourTable'[sales] ), _Dates )
)``````

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors