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.
