Annual Completion/Monthly Totals

[newtoallthis11]

New to this. Have done some training but seeking the best practice that isnt over complicated.

I have a dataset that contains end dates of specific items. For this instance, i will call them "hockey skates" and they are in a table called "equipment". Each skate component need to be "checked" and "approved" and will only have the date that this has been completed on and who they were completed by.

I am looking to have a datacard on a Power Bi that would show the following values when slicers are used:

Card 1 - Number of Blades "Checked" in a selected year. So if i have a slicer based on year, if i select 2023, i would get 2. If i selected 2024, i would get 1. I would have multiple cards for the other columns as well (boots Checked, Blades Approved, etc).

Chart - I would also like to be able to display in a line chart/bar graph, etc, the number that each inspector has done each month, that would start say in June of 2023 and would keep going as time goes on. To see the progress of work completed by each individual that was selected in a slicer.

Is the solution a combination of calculated columns and measures to display. Any guidance would be appreciated.

Skate S/N	Inspector	Blade Checked Date	Boot Checked Date	QC Approver	Blade Approved Date	Boot Approved Date
1	Tom	11/15/2023	11/28/2023	John	12/12/2023	01/15/2024
2	Steve	10/19/2023	01/12/2024	John	02/09/2024	03/15/2024
3	Rick	01/05/2024	01/09/2024	Dave	01/18/2024	06/19/2024

[Anonymous · Accepted solution]

Hi @newtoallthis11 ,

I expanded your sample data according to your needs:

And you need to add other two tables for year slicer and X-axis of line chart:

Use this DAX to create a calculated table:

Calendar = CALENDAR(DATE(2023,6,1), TODAY())

No relationship between them:

For your requirement Card 1:
Use these DAXs to create measures:

Blade Checked = 
VAR _year = SELECTEDVALUE(Year_slicer[Year])
RETURN
IF(
    ISFILTERED(Year_slicer[Year]),
    CALCULATE(
        COUNT('Table'[Blade Checked Date]),
        YEAR('Table'[Blade Checked Date]) = _year
    ),
    COUNT('Table'[Blade Checked Date])
)

Boot Checked = 
VAR _year = SELECTEDVALUE(Year_slicer[Year])
RETURN
IF(
    ISFILTERED(Year_slicer[Year]),
    CALCULATE(
        COUNT('Table'[Boot Checked Date]),
        YEAR('Table'[Boot Checked Date]) = _year
    ),
    COUNT('Table'[Boot Checked Date])
)

Output:

For your requirement Chart:
Use this DAX to create a measure:

_count = 
CALCULATE(
    COUNT('Table'[Blade Checked Date]),
    YEAR('Table'[Blade Checked Date]) = YEAR(MAX('Calendar'[Date])) && MONTH('Table'[Blade Checked Date]) = MONTH(MAX('Calendar'[Date]))
)

Output:

Please modify DAX to calculate other columns.

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