Forum Discussion
Solved
Dynamic Grouping by Date Similar to Excel
Hello Everyone, I am trying to recreate a report that pulls data out of an Access DB into Excel then pivots are created from said data. I am pulling the data directly from the data source instea...
Hi Greg_Z,
This should help you. https://drive.google.com/file/d/1ArLIICzpmJjmDqs7EVyIm8_DMENOlqqj/view?usp=sharing
If you follow the steps in the query editor it will get you the desired results. Add the group and the index to the visualization. Then sort the visual on the elipsis menu.
davehus
Memorable Member
Memorable MemberHi, see code below to give you what you need.
Period Grouping C =
IF(
YEAR(
DateTable[DateKey]
)<YEAR(
TODAY()
),
CONCATENATE(
"< 1/1/",
YEAR(
TODAY()
)
),
FORMAT(
DATEVALUE(
DateTable[DateKey]
),
"YYYY MMMM"
)
)
Hi Davehaus,
Thank you for the reply! However, I have already tried that and it does not work because I cannot sort by the correct year and month. The periods need to show in the correct order (i.e. < 1/1/2022 then 2022 January then 2022 February then 2022 March).