Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
122 | |
101 | |
71 | |
61 |