topic Re: For each month within a timespan create a new entry in an new table in DAX Commands and Tips

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

Anonymous — Sun, 11 Sep 2022 19:29:12 GMT

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!

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

Anonymous — Mon, 12 Sep 2022 02:48:47 GMT

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.

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

Anonymous — Mon, 12 Sep 2022 10:38:15 GMT

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!

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

Anonymous — Tue, 13 Sep 2022 05:10:09 GMT

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.

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

Anonymous — Tue, 13 Sep 2022 07:18:49 GMT

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