Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Team,
I am looking at generating a series of columns based on a table as per below.
Table:
Trading Name | Open Date | End Date |
Centre 1 | 2/05/2020 | 2/08/2020 |
Centre 2 | 24/08/2020 | 24/11/2020 |
Results Table:
Trading Name | Date | Month |
Centre 1 | 2/05/2020 | 1 |
Centre 1 | 2/06/2020 | 2 |
Centre 1 | 2/07/2020 | 3 |
Centre 1 | 2/08/2020 | 4 |
Centre 2 | 24/08/2020 | 1 |
Centre 2 | 24/09/2020 | 2 |
Centre 2 | 24/10/2020 | 3 |
Centre 2 | 24/11/2020 | 4 |
Now I was able to generate the table using the below guide but can't figure out how to add the series (Month) column.
New Table =
SELECTCOLUMNS(
FILTER(
GENERATE(
'Table',
GENERATESERIES(MIN('Table'[Open Date]), MIN('Table'[End Date]), 1)
),
[Value] >= [Open Date]
&& [Value] <= [End Date]
),
"Label", [Trading Name],
"Value", [Value]
)
Cheers,
Mark
Solved! Go to Solution.
Thanks for all the responses. I was able to figure this out by just generating the series with the above dax query I mentioned. I then just created a rank column to get the sequence of dates.
@markjdm_17 - If I am understanding this correctly, try this:
New Table =
SELECTCOLUMNS(
FILTER(
GENERATE(
'Table',
ADDCOLUMNS(
GENERATESERIES(MIN('Table'[Open Date]), MIN('Table'[End Date]), 1),
"Month",MONTH([Value])
)
),
[Value] >= [Open Date]
&& [Value] <= [End Date]
),
"Label", [Trading Name],
"Value", [Value]
)
@markjdm_17 , refer to my HR blog. Seems that can help
Thanks for all the responses. I was able to figure this out by just generating the series with the above dax query I mentioned. I then just created a rank column to get the sequence of dates.
Hi @markjdm_17 ,
Glad to hear that. Please accept your reply as a solution so that people who may have the same question can get the solution directly. Your contribution is highly appreciated.
Best Regards,
Icey
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |