Skip to main content
cancel
Showing results for 
Search instead 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.

Reply
Aradhana
Helper I
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.

dategroupsales
1/1/2023A10
1/2/2023A 
1/3/2023A 
1/4/2023A20
1/5/2023A30
1/5/2023A30
1/6/2023A 
1/7/2023A10
1/7/2023A10
1/9/2023B10
1/9/2023B20
1/10/2023B 
1/11/2023B60
1/12/2023B10
1/12/2023B30
1/13/2023C 
1/14/2023C10
1/15/2023C10
1/16/2023C10

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: 

CategoryLast 3 occurrances datesales value
A7th Jan, 5th Jan, 4th JanAvg(10,10,30,30,20)
B12th Jan ,11th Jan, 9th JanAvg(30,10,60,20,10)
Cthere are no 3 occurrances0

 

Thanks,

Aradhana

1 ACCEPTED SOLUTION
jdbuchanan71
Super User
Super User

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

jdbuchanan71_0-1703135115082.png

 

View solution in original post

6 REPLIES 6
jdbuchanan71
Super User
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.

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 )
    )
 
Aradhana_0-1705464087857.png

Thanks,

Aradhana

jdbuchanan71
Super User
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?

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,

Aradhana_0-1704188384548.png

Please let me know If you still have questions.

 

Thanks,

Aradhana

Aradhana
Helper I
Helper I

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_numberhose_socket_crimp_typemeasurement_descriptiondatediameter
SFX02RUFIX036H0376793171-10-1172/13512-THose End Crimp Diameter3/27/20230.575
SFX02RUFIX036H0376793171-10-1172/13512-THose End Crimp Diameter3/27/20230.575
SFX02RUFIX036H0376793171-10-1172/13512-THose End Crimp Diameter3/27/20230.575
SFX02RUFIX036H0378380171-10-1172/13512-THose End Crimp Diameter3/27/20230.575
SFX02RUFIX036H0378380171-10-1172/13512-THose End Crimp Diameter3/27/20230.575
SFX02RUFIX036H0378380171-10-1172/13512-THose End Crimp Diameter3/27/20230.575
SFX02RUFIX036H0378378171-10-1172/13512-TConnection End Crimp Diameter4/3/20231.5765
SFX02RUFIX036H0378378171-10-1172/13512-TConnection End Crimp Diameter4/3/20231.5765
SFX02RUFIX036H0378378171-10-1172/13512-TConnection End Crimp Diameter4/3/20231.5765
SFX02RUFIX036H0378378171-10-1172/13512-THose End Crimp Diameter4/3/20230.575
SFX02RUFIX036H0378378171-10-1172/13512-THose End Crimp Diameter4/3/20230.575
SFX02RUFIX036H0378378171-10-1172/13512-THose End Crimp Diameter4/3/20230.575
SFX02RUFIX036H0377096171-10-1172/13512-TConnection End Crimp Diameter4/4/20231.5765
SFX02RUFIX036H0377096171-10-1172/13512-TConnection End Crimp Diameter4/4/20231.5765
SFX02RUFIX036H0377096171-10-1172/13512-TConnection End Crimp Diameter4/4/20231.5765
SFX02RUFIX036H0377096171-10-1172/13512-THose End Crimp Diameter4/4/20230.575
SFX02RUFIX036H0377096171-10-1172/13512-THose End Crimp Diameter4/4/20230.575
SFX02RUFIX036H0377096171-10-1172/13512-THose End Crimp Diameter4/4/20230.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
Super User
Super User

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

jdbuchanan71_0-1703135115082.png

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

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

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors