For each month within a timespan create a new entry in an new table

[Anonymous]

Hi. I defently need some help, after searching some hours for a solution by myself.

For example, if I start with this table which could be the startdate and enddate of an contract:

id	start	end
1	1/1/2020	3/31/2020
2	2/1/2020	4/30/2020

I am looking for a table, which tells me for each month that the contract is still running.

id	time_point
1	1/1/2020
1	2/1/2020
1	3/1/2020
2	2/1/2020
2	3/1/2020
2	4/1/2020

With this table , I would be able to make a view how many contracts are running each month.

Thanks everybody!

[Anonymous · Accepted solution]

Hi, thanks for staying.

I finnaly found a solution by myself. I did is this way. I 

This was the starting point

= Table.AddColumn(ET_Contract_Generation_Asset__c, "Duration_Days", each List.Dates(DateTime.Date([Start_Date__c]),Duration.Days(DateTime.Date([End_Date__c]) -DateTime.Date([Start_Date__c])), #duration(1,0,0,0)))

Transform all dates to the first

Table.AddColumn(#"Hinzugefügte benutzerdefinierte Spalte", "Duration_Days_First_Of_Month", each List.Transform([Duration_Days],each Date.StartOfMonth(_)))

Delete Duplicates

Table.AddColumn(#"Hinzugefügte benutzerdefinierte Spalte1", "Duration_Months", each List.Distinct([Duration_Days_First_Of_Month]))

This way the memory and calucation isn't collapsing.

Many thanks

[Anonymous]

Hi @Anonymous ,

I have create a simple sample , please refer to it to see if it helps you.

Add a custom column in Power Query.

List.Dates(DateTime.Date([start]),Duration.Days(DateTime.Date([end]) -DateTime.Date([start])), #duration(1,0,0,0))

Then expand the custom column.

Finally create a measure.

Measure =
VAR _year =
    YEAR ( MAX ( 'Table'[Custom] ) )
VAR _month =
    MONTH ( MAX ( 'Table'[Custom] ) )
RETURN
    IF (
        MAX ( 'Table'[Custom] ) = DATE ( _year, _month, 1 ),
        MAX ( 'Table'[Custom] ),
        BLANK ()
    )

Best Regards

Community Support Team _ Polly

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

[Anonymous]

Great Polly, this works very well so far.

However, by this way a recieve a table with millons of entries in the first step, because each day is an entry.

With the second step, we eliminate those millions by filtering only the month's first.

Is there an alternative way to filter them in the first place before creating a table. This way consumes a lot space and calucation time.

Many thanks!

[Anonymous]

Hi @Anonymous ,

What is your meaning? Could you please provide a simple like the above? Or some screenshots.

Best Regards

Community Support Team _ Polly

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