Count the Number of Project for Each Month

[Kish1999]

Hello All,

I have requirement where I want to count the number of Projects in a particular status Month Over Month. Below is the sample dataset.

Project	Created Date	Modified date	Status
A	01-02-2020	01-02-2020	Green
A	01-02-2020	04-04-20 20	Red
A	01-02-2020	07-08-2020	Amber
B	02-02-2020	02-02-2020	Green
B	02-02-2020	04-07-2020	Amber
C	03-02-2020	03-02-2020	Green
C	03-02-2020	07-02-2020	Amber
C	03-02-2020	20-02-2020	Red

The requirement is to Count the Prjects with Red,Amber,Green status for each month. If the project status changes multiple times within a month, we should count the latest status. Below is the sample result:

Result:
Month	Green	Amber	Red
Feb-20	2	0	1
Mar-20	2	0	1
Apr-20	1	0	2
May-20	1	0	2
Jun-20	1	0	2
Jul-20	1	0	2
Aug-20	1	1	1

[v-rongtiep-msft · Accepted solution]

Hi @Kish1999,

Please have a try.

Modify the data foramt.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTLSN9Q3MjAyQGW6F6Wm5inF6mCoMdE3gTGDUlOwqbDQN4cxHXOTUovAapzAaowQNhlh2oSmxhxhE8IcZ7AaY4Q5xpjmYKjB4h4MNUYGKN6KBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, #"Created Date" = _t, #"Modified date" = _t, Status = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"Created Date", type date}, {"Modified date", type date}, {"Status", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if 
List.Count(
List.Select(
List.Dates([Created Date],Duration.Days([Modified date]-[Created Date])+1,#duration(1,0,0,0)),each Date.Day(_)=1))
= 0 
then {[Modified date]}
else List.Select(
List.Dates([Created Date],Duration.Days([Modified date]-[Created Date])+1,#duration(1,0,0,0)),each Date.Day(_)=1)),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}})
in
    #"Changed Type1"

Then create measures.

Measure3 =
MAXX (
    FILTER (
        ALL ( 'Table' ),
        'Table'[Project] = SELECTEDVALUE ( 'Table'[Project] )
            && 'Table'[Column] = SELECTEDVALUE ( 'Table'[Column] )
    ),
    [Measure2]
)

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.