Missing values when grouping

[Anonymous]

Hello everone. First post - tell me if i'm doing smth wrong-

I am currently working on a quarterly report where I have to group a lot of data (45.000 rows by 85 columns) by date into groups of four month intervals (the period June 1st 2021 10:00:00 AM - October 1st 09:59:59 AM 2021, October 1st 2021 10:00:00 AM - February 2022 09:59:59 AM etc. 

I am btw using european time in my data and my formatting but just chose to write the american time format here.

The column with the date data is formatted as date/time and looks right. I right click the column and choose New group, select 'List' as group type and then click the first date of the interval, scroll down to the last date of the interval and shift click it to mark all values in between. After doing this to all the data, nothing is left in the list of non-grouped values. However when I then try to create a visualization using the grouped dates it has grouped several thousand values in to a Blank group. 

TL;DR My grouping doesnt include all the data in the groups even though it appears to do so. How can I make sure to inclucde all the data? 

[Anonymous · Accepted solution]

Hi @Anonymous ,

One possible way to do that is to use DAX formulas to create a new column that assigns each date to a specific interval based on your criteria.

To use DAX formulas, you need to:

Select your data and go to Modeling > New Column.
Create two columns:

Date1 = [Date]-10/24

Date Interval = SWITCH(TRUE(),

'Table'[Date1] >= DATE(2021,6,1) && 'Table'[Date1] < DATE(2021,10,1), "Jun-Oct 2021",

'Table'[Date1] >= DATE(2021,10,1) && 'Table'[Date1] < DATE(2022,2,1), "Oct-Feb 2022",

......

)

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

[Anonymous]

Thank you Stephen!

This is exactly the solution I ended up going with. It definitely solves the problem.

Doesn't answer the question of why my data isn't being grouped right though, but I'll figure that out another time. 

Thanks