How to count records in a range dates per month

[MaNUEL2019]

Hi Guys, 

Maybe you can help me on this...

I want to know how many machines per month I have in a customer based on this table

machine Serial number	Customer	Init date	End date
42509	A	01/01/2019	30/03/2109
42509	B	01/04/2019	18/02/2020
42510	A	01/02/2019
87836	C	01/01/2019

I want to have a result like this...

	Customer A
	ene-19	feb-19	mar-19	abr-19	may-19
Machines	1	2	2	1	1

I have tried with summarize but I cannot adjust by period... 

Any help...

Thanks in advance

Regards.

[v-alq-msft · Accepted solution]

Hi, @MaNUEL2019 

Firstly, you can create a calculated table as follows.

Dates = CalendarAuto()

Secondly, you may create two columns and a measure as below.

FormatDate = FORMAT([Date],"MMM") & "-" & FORMAT('Dates'[Date],"YY")

Machines = "Machines"

Amount =

CALCULATE (

    DISTINCTCOUNT ( 'Table'[machine Serial number] ),

    FILTER (

        FILTER(ALLSELECTED ( 'Table' ),'Table'[Customer] IN FILTERS ( 'Table'[Customer] )),

        OR (

            [Init date] <= MAX ( 'Dates'[Date] )

                && ISBLANK ( 'Table'[End date] ),

            NOT (

                OR ( [Init date] >= MAX ( 'Dates'[Date] ), [End date] <= MIN ( 'Dates'[Date] ) )

            )   

        )

    )

)

Result:

If I misunderstand your thought, please show me your expected output. I am glad to solve the problem.

Best Regards,

Allan

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

[KEITHO]

I don't think you will be able to do it until you reformat the data.

Each date between init date and end date will have to be associated to the customer and machine serial number. So for example your data should have 89 records for machine serial 42509 for customer A. Alternatively you need at least one record per month present.

You can do that with a linked date range table. There's another post here which gives a pretty good outline of using dates:

https://community.powerbi.com/t5/Desktop/Group-dates-to-Month/m-p/166986#M72916