Count for every month in the date interval

[TrulsB]

I have a table with service jobs.  The fields is "Title", "Start date", "Stop date". 

eg. 

Title                                                         Start date                   Stop date

"Service on production machine A.        01.01.2020                  01.03.2020

The production machine A is not available in the service period (between the start and stop date).  I have a lot of service jobs, like this. 

I like to count the number of service jobs in the period, eg.  Januar, February, March, April  or based on week number, and for the example this service job should be included in the number of service jobs, for the whole time periode.   If sum on a month, i like this job to be counted in both january, february and march.  

How should the DAX expression look like to count this for every time period between the dates. Any idea ?

[v-juanli-msft · Accepted solution]

Hi @TrulsB 

Create a date table ( this date table has no relationship with your table)

DATE =
ADDCOLUMNS (
    CALENDARAUTO (),
    "year", YEAR ( [Date] ),
    "quarter", SWITCH (
        TRUE,
        MONTH ( [Date] )
            IN {
            1,
            2,
            3
        }, "Q1",
        MONTH ( [Date] )
            IN {
            4,
            5,
            6
        }, "Q2",
        MONTH ( [Date] )
            IN {
            7,
            8,
            9
        }, "Q3",
        MONTH ( [Date] )
            IN {
            10,
            11,
            12
        }, "Q4"
    ),
    "monthname", FORMAT (
        [Date],
        "mmm"
    ),
    "monthno", FORMAT (
        [Date],
        "yyyy-mm"
    ),
    "week", WEEKNUM (
        [Date],
        2
    )
)

Create calcualted columns in this date table

start of month = STARTOFMONTH('DATE'[Date])

end of month = ENDOFMONTH('DATE'[Date])

start of quarter = STARTOFQUARTER('DATE'[Date])

end of quarter = ENDOFQUARTER('DATE'[Date])

start of year = STARTOFYEAR('DATE'[Date])

end of year = ENDOFYEAR('DATE'[Date])

Add columns from Date table to the X-axis of the visual as below

Then create measures

day level =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[machine] ),
    FILTER (
        'Table',
        'Table'[Start date]
            <= MAX ( 'DATE'[Date] )
            && 'Table'[Stop date]
                >= MAX ( 'DATE'[Date] )
    )
)

month level =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[machine] ),
    FILTER (
        'Table',
        'Table'[Stop date]
            >= MAX ( 'DATE'[start of month] )
            && 'Table'[Start date]
                <= MAX ( 'DATE'[end of month] )
    )
)


quarter level =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[machine] ),
    FILTER (
        'Table',
        'Table'[Stop date]
            >= MAX ( 'DATE'[start of quarter] )
            && 'Table'[Start date]
                <= MAX ( 'DATE'[end of quarter] )
    )
)

year level =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[machine] ),
    FILTER (
        'Table',
        'Table'[Stop date]
            >= MAX ( 'DATE'[start of year] )
            && 'Table'[Start date]
                <= MAX ( 'DATE'[end of year] )
    )
)


Final result =
IF (
    ISINSCOPE ( 'DATE'[Date] ),
    [day level],
    IF (
        ISINSCOPE ( 'DATE'[week] ),
        [day level],
        IF (
            ISINSCOPE ( 'DATE'[monthname] ),
            [month level],
            IF (
                ISINSCOPE ( 'DATE'[quarter] ),
                [quarter level],
                [year level]
            )
        )
    )
)

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-juanli-msft]

Hi @TrulsB 

Do you have tables like this?

Title	Start date	Stop date
"Service on production machine A.	1/1/2020	1/3/2020
"Service on production machine B.	1/10/2020	3/28/2020

You want to calculate counts for machine A,B,C..,ect in date periods (year,month,week,,ect), right?

i have a doubt that you say "The production machine A is not available in the service period (between the start and stop date)",

is this mean the counts should not include the machine A?

But "for the example this service job should be included in the number of service jobs, for the whole time periode",

I don't understand well about your statement and requirements.

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TrulsB]

eg.

If i create a graph for every day from 1st january to 38th march, the Machine A should be in the graph for 1st, 2nt and 3rd january, and Machine B should be in the graph for every day from 10 january to 28 march. 

[TrulsB]

Machina A should be counted in the period between 1th January and 1th March, and machine B should be counted in the time periode between 10. January and 28th March.

[amitchandak]

Refer, if this can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin