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

[Aradhana]

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,

Aradhana

[jdbuchanan71]

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.

[Aradhana]

Hi @jdbuchanan71 ,

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,

Aradhana

[jdbuchanan71]

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?

[Aradhana]

Hi @jdbuchanan71,

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.

For your reference,

Please let me know If you still have questions.

Thanks,

Aradhana

[Aradhana]

Hi @jdbuchanan71 ,

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 in advance.

Thanks,

Aradhana 

[jdbuchanan71 · Accepted solution]

@Aradhana 

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