Forum Discussion

sixelapowerbi's avatar
sixelapowerbi
Regular Visitor
3 years ago
Solved

monthly KPI tracking

 

Hello everyone,

 

I'm coming to you with a question about monthly KPI tracking. I haven't found any answers to my problems on the forums/internet so I'm taking the liberty of posting.

 

My need is to have monthly KPI tracking for my projects.

There are 4 possible statuses for a project: open and in-time; close in-time; open and late; close late.

A project is late if it is above target.
I'd like to have a monthly view that counts the number of projects for each status over the months.

 

Here's an example:

 

IDStart (Date)End (Date)Target (Days)
00127.02.202315.04.2023

        10

00201.03.202315.06.2023        10
00305.07.2023NULL        10
00410.04.202312.04.2023         5


And here is the desired result:

IDJanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecember
0010open and in-timeopen and lateclose late00000000
00200open and lateopen and lateopen and lateclose late000000
003000000open and in-time00000
004000close in-time00000000

 

 

Note : 

We are the 5th of July (05/07/2023) then the project '003' is 'In-time'. This means that for the current month, we take the current date.
For previous months, we take the last day of the month. --> That's why project '001' was 'in-time' in February.

 

I hope I've been as clear as possible.

Thanks in advance for the help.

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi sixelapowerbi ,

    Please refer to my pbix file to see if it helps you.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XYzBDcAwCAN38TsSYJJmmIj912h5EKH+Tr6Tz4GqYYBKFwp38RRbH5siRkYs4XL759d4iS25dzXbrzbO24WIFw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Start date" = _t, #"End Date" = _t, Target = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Start date", type date}, {"End Date", type date}, {"Target", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Transform({Number.From([Start date])..let a = if Number.From([End Date]) = null then Number.From([Start date]) else Number.From([End Date]) in a},each Date.From(_))),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom")
in
    #"Expanded Custom"

    Then create a measure.

    Measure =
VAR _yuedi =
    EOMONTH ( MAX ( 'Table'[Custom] ), 0 )
VAR _today =
    MONTH ( TODAY () )
VAR _starttoend =
    DATEDIFF ( MAX ( 'Table'[Start date] ), MAX ( 'Table'[End Date] ), DAY )
VAR _starttoyuedi =
    DATEDIFF ( MAX ( 'Table'[Start date] ), _yuedi, DAY )
VAR _maxmonth =
    CALCULATE (
        MAX ( 'Table'[_month] ),
        FILTER ( ALL ( 'Table' ), 'Table'[ID] = SELECTEDVALUE ( 'Table'[ID] ) )
    )
VAR _minmonth =
    CALCULATE (
        MIN ( 'Table'[_month] ),
        FILTER ( ALL ( 'Table' ), 'Table'[ID] = SELECTEDVALUE ( 'Table'[ID] ) )
    )
RETURN
    IF (
        MAX ( 'Table'[End Date] ) <> BLANK ()
            && MAX ( 'Table'[Target] ) >= _starttoend,
        "Close in time",
        IF (
            MAX ( 'Table'[_month] ) = _today
                && MAX ( 'Table'[End Date] ) = BLANK (),
            "Open and in time",
            IF (
                _starttoyuedi > MAX ( 'Table'[Target] )
                    && MAX ( 'Table'[_month] ) < _maxmonth,
                "Open and late",
                IF (
                    _starttoyuedi > MAX ( 'Table'[Target] )
                        && MAX ( 'Table'[_month] ) = _maxmonth,
                    "Close late",
                    IF (
                        _starttoyuedi <= MAX ( 'Table'[Target] )
                            && _minmonth = SELECTEDVALUE ( 'Table'[_month] ),
                        "Open and in time",
                        IF (
                            _starttoyuedi <= MAX ( 'Table'[Target] )
                                && _maxmonth = SELECTEDVALUE ( 'Table'[_month] )
                                && _starttoend > MAX ( 'Table'[Target] ),
                            "Close late",
                            IF (
                                _starttoyuedi <= MAX ( 'Table'[Target] )
                                    && _minmonth <= SELECTEDVALUE ( 'Table'[_month] )
                                    && _maxmonth >= SELECTEDVALUE ( 'Table'[_month] )
                                    && _starttoend > MAX ( 'Table'[Target] ),
                                "Open and late",
                                BLANK ()
                            )
                        )
                    )
                )
            )
        )
    )

     

    How to Get Your Question Answered Quickly 

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Rongtie

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

     

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi sixelapowerbi ,

    Please refer to my pbix file to see if it helps you.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XYzBDcAwCAN38TsSYJJmmIj912h5EKH+Tr6Tz4GqYYBKFwp38RRbH5siRkYs4XL759d4iS25dzXbrzbO24WIFw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Start date" = _t, #"End Date" = _t, Target = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Start date", type date}, {"End Date", type date}, {"Target", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Transform({Number.From([Start date])..let a = if Number.From([End Date]) = null then Number.From([Start date]) else Number.From([End Date]) in a},each Date.From(_))),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom")
in
    #"Expanded Custom"

    Then create a measure.

    Measure =
VAR _yuedi =
    EOMONTH ( MAX ( 'Table'[Custom] ), 0 )
VAR _today =
    MONTH ( TODAY () )
VAR _starttoend =
    DATEDIFF ( MAX ( 'Table'[Start date] ), MAX ( 'Table'[End Date] ), DAY )
VAR _starttoyuedi =
    DATEDIFF ( MAX ( 'Table'[Start date] ), _yuedi, DAY )
VAR _maxmonth =
    CALCULATE (
        MAX ( 'Table'[_month] ),
        FILTER ( ALL ( 'Table' ), 'Table'[ID] = SELECTEDVALUE ( 'Table'[ID] ) )
    )
VAR _minmonth =
    CALCULATE (
        MIN ( 'Table'[_month] ),
        FILTER ( ALL ( 'Table' ), 'Table'[ID] = SELECTEDVALUE ( 'Table'[ID] ) )
    )
RETURN
    IF (
        MAX ( 'Table'[End Date] ) <> BLANK ()
            && MAX ( 'Table'[Target] ) >= _starttoend,
        "Close in time",
        IF (
            MAX ( 'Table'[_month] ) = _today
                && MAX ( 'Table'[End Date] ) = BLANK (),
            "Open and in time",
            IF (
                _starttoyuedi > MAX ( 'Table'[Target] )
                    && MAX ( 'Table'[_month] ) < _maxmonth,
                "Open and late",
                IF (
                    _starttoyuedi > MAX ( 'Table'[Target] )
                        && MAX ( 'Table'[_month] ) = _maxmonth,
                    "Close late",
                    IF (
                        _starttoyuedi <= MAX ( 'Table'[Target] )
                            && _minmonth = SELECTEDVALUE ( 'Table'[_month] ),
                        "Open and in time",
                        IF (
                            _starttoyuedi <= MAX ( 'Table'[Target] )
                                && _maxmonth = SELECTEDVALUE ( 'Table'[_month] )
                                && _starttoend > MAX ( 'Table'[Target] ),
                            "Close late",
                            IF (
                                _starttoyuedi <= MAX ( 'Table'[Target] )
                                    && _minmonth <= SELECTEDVALUE ( 'Table'[_month] )
                                    && _maxmonth >= SELECTEDVALUE ( 'Table'[_month] )
                                    && _starttoend > MAX ( 'Table'[Target] ),
                                "Open and late",
                                BLANK ()
                            )
                        )
                    )
                )
            )
        )
    )

     

    How to Get Your Question Answered Quickly 

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Rongtie

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