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.
I'm trying to add a custom column to my table that would dynamically list the days of last month.
Example:
In October the table would have rows:
In November:
I've been drilling into the Date functions (documented here) but can't see one that would cater to this requirement.
Is this function possible?
Solved! Go to Solution.
I ended up merging both queries based on the date column. After expanding the merge, filtering some blank/"null" rows, removing duplicates and filtering appropriately I was able to cobble together my solution.
@Anonymous,
What is the correct answer for when the previous month exceeds the current month, e.g. Nov has 30 days where Oct has 31? Also notice rows 30 & 31 where Sept 30 is repeated.
Did you try Date.AddMonths ( )?
Of course I am presuming you have a Date column with a contiguous date range.
Custom Column = Date.AddMonths ( [Date], -1 )
Proud to be a Super User!
I don't currently have a column of the current month. I'm happy to add whatever else I need to make this work though.
Edit: I should state that I don't know of a function that just lists all of the days of the current month. I anticipate the same issue you raised for months when there are 30 vs 31 days and using a function that is (date) - 1.
The issue I'm trying to solve is that I have 2 sets of data, with no unique fields to create a relationship. So I was trying to create some sort of "joiner" table of data that could hold unique dates and relate those to the duplicate dates in each other table.
I suppose another thing I could investigate is copying a column from another table and just removing duplicates. Bit hacky but it might serve..
I ended up merging both queries based on the date column. After expanding the merge, filtering some blank/"null" rows, removing duplicates and filtering appropriately I was able to cobble together my solution.
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 |
---|---|
112 | |
97 | |
84 | |
67 | |
61 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |