Completed Per Week

[Anonymous]

Hi, I am currently trying to work out how to calculate number of completed jobs per week, I have the following formula

No. Jobs Completed =

CALCULATE(

    COUNTROWS('Job Tracker'),

    FILTER(

        'Job Tracker',

        'Job Tracker'[Days on Site]="Completed"

    )

)+0

so for an example from 4th to 10th May there are 2 completed jobs on the 8th May the answer wold be 2 if I clicked on the 10th May date tile slicer (calculated column: 

Week Ending = 'Job Tracker'[Created]- MOD('Job Tracker'[Created]-1,7)+5)

any assistance would be appreciated

[Anonymous]

all the data is from a sharepoint list, we could either use measures days on Site = completed or completed date cannot be blank etc, theres a field JOb No which is the job ID that needs to be counted

sharepoint currently filtered to days on site completed

[Anonymous]

Hi @Anonymous ,

As @ExcelMonke @Wilson_  said, create a date table is the best option.

Apply the weekend formula you created to the date table. Refer to the demo below:

weekend = 'Date'[Date]- MOD('Date'[Date]-1,7)+5

No. Jobs Completed = 
CALCULATE (
    COUNTROWS ( 'Job Tracker' ),
    FILTER ( 'Job Tracker', 'Job Tracker'[Days on Site] = "Completed" )
) + 0

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ExcelMonke]

Agreed with @Wilson_  here. Look into creating a date table, and have a week beginning (or week ending) column, which returns the first/last date of each week. Then, what you can do is use the follwing measure:

CALCULATE(
COUNTROWS('Job Tracker'),
ALLEXCEPT(DateTable,DateTable[WeekBeginning])
)

[Wilson_]

Hi SmartZ96,

Do you have a date dimension table in your data model, with week related columns in it? If not, I would look into that. It would make this infinitely easier.

This is the most comprehensive date dimension table I've come across so far.

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.