Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Greg_Z
Regular Visitor

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 instead of Access so that we can retire the Access DB.  What I am trying to do is a grouping based on month similar to what you can do in an Excel Pivot table.  In the screenshot below you can see that a Starting at and Ending at date is defined (I would like this to be dynamic), the grouping is done by month, and the resulting data is grouped by the Ending at year and showing < 1/1/[Starting at year] for all periods before the Ending at year.  Again, I would prefer this to be dynamic, and would also prefer to show the month name instead of number.  I have figured out that I can create a YYYY MM field that changes dynamically, but that does not give me the month name.  Please let me know if any of this makes sense and if more information is needed.

 

Excel Example

 

Greg_Z_0-1647441890137.png

 

Power BI Formula Without Month Name

 

Period Grouping =

IF(
YEAR(
[Date]
)<YEAR(
TODAY()
),
CONCATENATE(
"< 1/1/",
YEAR(
TODAY()
)
),
FORMAT(
DATEVALUE(
[Date]
),
"YYYY MM"
)
)

 

Power BI Example Without Month Name

 

Greg_Z_2-1647441963907.png

 

1 ACCEPTED SOLUTION

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_0-1647531565182.png

 

View solution in original post

8 REPLIES 8
davehus
Memorable Member
Memorable Member

I should just further add that as this is a calculated column, it is computed in advance, therefore if you go back to last year, you won't get the <1/1 prefix for 2020.

davehus
Memorable Member
Memorable Member

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

davehus_0-1647479901713.png

 

 

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

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. 

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!

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_0-1647531565182.png

 

Thank you Davehus!  I appreciate all of the help!

Hi @Greg_Z , No problem, a pleasure. Happy St Patricks Day 🙂 

 

D

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.