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 group an invoice period in starting on the 14th of a month and ending on the 13th of the following month.
Date GROUP
5/14/2021 | GROUP 1 |
5/15/2021 | GROUP 1 |
… | GROUP 1 |
6/12/2021 | GROUP 1 |
6/13/2021 | GROUP 1 |
6/14/2021 | GROUP 2 |
6/15/2021 | GROUP 2 |
… | GROUP 2 |
7/12/2021 | GROUP 2 |
7/13/2021 | GROUP 2 |
Thanks in advance.
Solved! Go to Solution.
@Anonymous
You can add the following code as a new Calculated Column:
Group DAX =
var __group =
RANKX(
ALL('Table'),
var __date = [Date] return
IF( DAY(__date) < 14, EOMONTH(__date,-2)+14, EOMONTH(__date,-1)+14 )
,,ASC,Dense
)
return
"Group " & __group
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Table.Group() does the trick,
let
Source = Table.FromList(List.Dates(#date(2021,5,14),55,#duration(1,0,0,0)), Splitter.SplitByNothing(), {"Date"}),
#"Grouped Rows" = Table.Group(Source, {"Date"}, {"Group", each _}, 0, (x,y) => Number.From(Date.Day(y[Date])=14))
in
#"Grouped Rows"
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
@Anonymous
You can do it in Power Query. I attached a file, please check the steps.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
That is Awesome! However, the source is a calculated table. Can it be done in DAX?
@Anonymous
I will try and share the code soon. 👍
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Anonymous
You can add the following code as a new Calculated Column:
Group DAX =
var __group =
RANKX(
ALL('Table'),
var __date = [Date] return
IF( DAY(__date) < 14, EOMONTH(__date,-2)+14, EOMONTH(__date,-1)+14 )
,,ASC,Dense
)
return
"Group " & __group
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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 |
---|---|
106 | |
94 | |
75 | |
62 | |
50 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |