Forum Discussion

Greg_Z's avatar
Greg_Z
Regular Visitor
4 years ago
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...
Need Help
davehus's avatar
davehus
Memorable Member
4 years ago

Hi, 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"
)
)

 

 

Greg_Z's avatar
Greg_Z
Regular Visitor
4 years ago

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).

  • davehus's avatar
    davehus
    Memorable Member
    4 years ago

    Hi, there's a couple ways around this. You can try and sort the calculated column by the date column in the ribbon. Another way would be to do everything in Power query and create an index key to sort by. 

    • Greg_Z's avatar
      Greg_Z
      Regular Visitor
      4 years ago

      Hi Davehus,

       

      I will look into Power Query and indexing because you cannot sort by the date column in the ribbon because there are multiple period values per date; they have to be 1-to-1 (I tried that already as well).  Thank you for the advice!