How to calculate completion over a rolling 7 days?

[A_Scott]

I have the following 2 calculations:

This one calculates how many inpsections are done to bakery ovens. It is used in a matrix that shows locations in the rows, date set to rolling 7 days in the columns, and the counts from the below calculation as the values.

Complete Bakery Oven Inspections = CALCULATE(

       COUNTA('BAKERY_OVEN_INSPECTION_LOG_DETAIL'[OVEN_INSPECTION_ID]))

The next calculation I have shows the total percent complete for the same rolling 7 days and is as follows:

% Bakery Oven Inspections Complete = IF([Oven Count]=0,

"N/A",

IF(

    ISINSCOPE('Open Locations'[Division-Store]),

    MIN(1,([Complete Bakery Oven Inspections]/([Oven Count]*7))),

    AVERAGEX(

        VALUES('Open Locations'[Division-Store]),

        MIN(1,[Complete Bakery Oven Inspections]/([Oven Count]*7))

    )

)

)

The problem I'm having is that my percent complete calculation does differientiate if a location does all of their inspections on a single day, or does one inspection each day as is required. So any location go falsely show 100% complete if as long as they did the 7. I want to calculate that each location is 100% complete each day, so I guess gives an average of the percent complete each day. How can I modify my % Bakery Oven Inspections Complete calculation to show the average percent complete each day?

[A_Scott · Accepted solution]

I was able to figure out the calculation using AverageX and Values.

[Anonymous]

Hi @A_Scott ,

Is it possible to provide relevant test data about your data model so that I can easily locate your error and provide relevant solutions.

Best regards,

Adamk Kong

[A_Scott]

Here are examples of my resouces. 

This is my primary resouce. In my star scema, everything else branches off from this list of equipment. It is my true list of equipment in my locations.

Division-Store	/Equipment Product Type Descr	/Unique Equipment ID
011-00695	OVEN	011-00695-3298377-OVEN
011-00696	OVEN	011-00696-3269665-OVEN
011-00697	OVEN	011-00697-3255180-OVEN
011-00697	OVEN	011-00697-3255181-OVEN
011-00699	OVEN	011-00699-3272815-OVEN
011-00703	OVEN	011-00703-3303547-OVEN
011-00703	OVEN	011-00703-3303548-OVEN
011-00730	OVEN	011-00730-3269920-OVEN
011-00730	OVEN	011-00730-3269921-OVEN
011-00867	OVEN	011-00867-3280965-OVEN
011-00979	OVEN	011-00979-3285966-OVEN

This is a list of the inspections perfromed in several locations. The employee manually enters in the unique equipment id. They don't always get this right and I am only counting the completed inspections that have accurate unique equipment numbers. They are also required to perform 1 inpsection everyday. So, if a location completes 7 inspections in a single day, and none on the others they will only get credit for 100% complete for the one day and 0% the other 6. which would make them only 14.29% complete.

OVEN_INSPECTION_ID	/Division-Store	/Equipment Type	/Unique Bakery Oven ID	/LOG_DATE	/INSPECTION_FAILED
15082	011-00703	Oven	011-00703-3303548-OVEN	1/1/2024	N
15083	011-00703	Oven	011-00703-3303547-OVEN	1/1/2024	N
15084	021-00410	Oven	021-00410-4427529-OVEN	1/1/2024	N
15086	011-00695	Oven	011-00695-3298378-OVEN	1/1/2024	N
15089	011-00695	Oven	011-00695-3298378-OVEN	1/1/2024	N
15090	011-00695	Oven	011-00695-123456-OVEN	1/1/2024	N
15091	016-00805	Oven	016-00805-3430511-OVEN	1/1/2024	N
15092	016-00805	Oven	016-00805-3430512-OVEN	1/1/2024	N
15095	024-00744	Oven	024-00744-4491593-OVEN	1/1/2024	N
15096	016-00623	Oven	016-00623-3449772-OVEN	1/1/2024	N
15097	016-00623	Oven	016-00623-3449773-OVEN	1/1/2024	N
15098	011-00671	Oven	011-00671-3251901-OVEN	1/1/2024	N
15099	014-00359	Oven	014-00359-4134000-OVEN	1/1/2024	N
15100	014-00359	Oven	014-00359-4133999-OVEN	1/1/2024	N
15101	011-00439	Oven	011-00439-3285829-OVEN	1/1/2024	N
15104	016-00920	Oven	016-00920-3450080-OVEN	1/1/2024	N
15105	016-00920	Oven	016-00920-3450079-OVEN	1/1/2024	N
15109	011-00485	Oven	011-00485-3277319-OVEN	1/1/2024	N
15113	011-00016	Oven	011-00016-3303275-OVEN	1/1/2024	N
15117	011-00413	Oven	011-00413-3274902-OVEN	1/1/2024	Y
15118	011-00413	Oven	011-00413-3274904-OVEN	1/1/2024	N
15123	034-00744	Oven	034-00744-3313724-OVEN	1/1/2024	N
15124	034-00744	Oven	034-00744-3313763-OVEN	1/1/2024	N
15126	615-00018	Oven	615-00018-4016498-OVEN	1/1/2024	N
15129	615-00056	Oven	615-00056-4204898-OVEN	1/1/2024	N
15131	018-00743	Oven	018-00743-8023210-OVEN	1/1/2024	N
15132	018-00743	Oven	018-00743-8023209-OVEN	1/1/2024	N

[A_Scott]

.What I'm looking for I guess is an average per day

[johnt75]

Try 

% Bakery Oven Inspections Complete =
VAR OvenCount = [Oven Count]
RETURN
    IF (
        OvenCount = 0,
        "N/A",
        VAR MaxDate =
            MAX ( 'Date'[Date] )
        VAR Result =
            AVERAGEX (
                VALUES ( 'Open Locations'[Division-Store] ),
                CALCULATE (
                    AVERAGEX (
                        DATESBETWEEN ( 'Date'[Date], MaxDate - 6, MaxDate ),
                        MIN ( 1, [Complete Bakery Oven Inspections] / OvenCount )
                    )
                )
            )
        RETURN
            Result
    )

I removed the IF ISINSCOPE because it was unneeded. If there is a single store in the filter context then VALUES will only return that store.

[A_Scott]

I was using inscope because my grand total without it was not the same as the sum of the rows. Will this Values calculation ensure all the sum of the rows will equal what Power BI tells me the grand total should be for that column?

[A_Scott]

This still showed a location that did not have an inspection everyday for the last 7 days as being 100%.

Their inspection count:

Date	# of Inspections
15-Nov	2
16-Nov
17-Nov
18-Nov
19-Nov	2
20-Nov	2
21-Nov	2
Total	8

[A_Scott]

If the Inspections are as follows:

Date - # Complete - Max % Complete

11/15 - 2 - 100%

11/16 - 0 - 0%

11/17 - 0 - 0%

11/18 - 0 - 0%

11/19 - 2 - 100%

11/20 - 2 - 100%

11/21 - 2 - 100%

% Complete for those 7 days would be the average of all equaling 57.14%?