Missing values when grouping

Question

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 Answer

Hi&nbsp;Anonymous&nbsp;,
&nbsp;
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 &gt; New Column.Create two columns:
Date1 = [Date]-10/24
&nbsp;
Date Interval = SWITCH(TRUE(),

'Table'[Date1] &gt;= DATE(2021,6,1) &amp;&amp; 'Table'[Date1] &lt; DATE(2021,10,1), "Jun-Oct 2021",

'Table'[Date1] &gt;= DATE(2021,10,1) &amp;&amp; 'Table'[Date1] &lt; DATE(2022,2,1), "Oct-Feb 2022",

......

)

&nbsp;
&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
Best Regards,
Stephen Tao
&nbsp;
If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly. &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;

Forum Discussion

Missing values when grouping

2 Replies

Related content

Fabric & Azure Data User Group Program Requirements & Benefits

Total value missing in calculated column

missing data

Grouping to include missing values

Generating Missing Data