M solution converted to DAX

[Anonymous]

Good day beautiful people,

I need a help with below solution, for now it is made in PQ. This is just one piece of bigger query, which unfortunately is causing some issues with performance so I would like to switch from PQ to DAX.

Date format: d/m/yyyy

What I need?

ID	Aging_Start_Date	End_Date
ABC123	1/1/2022	1/5/2022

Above table contains about 10k rows with unique IDs and dates for each.

What I need to present on visualization is how many IDs were aged per month. Main problem is that I have only start_date and end_date, so if my filters are between 1/2/2022 - 4/1/2022 this ID will be ignored or I will just see data for January and May.

I need to see data for Jan 1st, Feb 1st, Mar 1st, Apr 1st and May 1st (1 per month).

I'm using below PQ solution which works amazing but I would like to get rid of heavy calculations (merges etc.) from PQ and focus on DAX only.

let

AllDates = {Number.From([Aging_Start_Date])..Number.From([End_Date])},

StofMonthDates =

List.Distinct(

List.Transform(

AllDates, each Date.StartOfMonth(Date.From(_))
)
)

in
StofMonthDates

 Credits: Generate Dates between Start and End Date in Power Query - YouTube

Is there a way to receive similar result as with above query? Sample of the result is presented below.

ID	Aging_Start_Date	End_Date
ABC123	1/1/2022	1/5/2022

After query will be transformed to:

ID	StofMonthDates
ABC123	1/1/2022
ABC123	1/2/2022
ABC123	1/3/2022
ABC123	1/4/2022
ABC123	1/5/2022

Edit: I can't center the tables, sorry for this. HTML errors.

[amitchandak · Accepted solution]

@Anonymous , refer to the file of the blog. Both have little bit different approch

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-or-end-date/ba-p/1503785

[Anonymous]

Absolutely fantastic solution!
It's quite simple when you look at it but my brain could not figure it out.

Thank you!