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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sabilahmed
Resolver I
Resolver I

CROSSJOIN to obtain one row per month between start and end date column

Hi all,

 

I have used the following example to try and obtain the same results:

 

https://community.powerbi.com/t5/Desktop/Create-multiple-rows-for-dates-between-start-and-end-dates-...

 

Instead, I am getting one row for every single day between my start and end date. Why is this happening?

 

My new table now has millions and millions of rows. I only want one row per month between the start date and end date per contract.

 

Please help.

 

Many thanks,

Sabil

 

(@amitchandak - your help would be most appreciated).

1 ACCEPTED SOLUTION

I would need to see the fiscal month number.  If you don't have that then use 

 

SUMMARIZE(FILTER(...),[FY],[Month])

View solution in original post

12 REPLIES 12
Ashish_Mathur
Super User
Super User

Hi,

Just in case you wish to do this in the Query Editor, try this link - Generating Rows by Month for Date Ranges in Power Query | by Daniel Marsh-Patrick | Daniel Marsh-Pat...


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
lbendlin
Super User
Super User

You would put it around the second argument of the crossjoin ( the "FILTER(...)"  part).  In a pinch you can also use VALUES(SELECTCOLUMNS()) or DISTINCT()  - there are many ways to do this.

I'm sorry my DAX is not so great, could you show me example using my own DAX and paste it here?

I tried the GROUPBY statement before FILTER but I;m def doing something wrong. I get the error too few arguments.

 

Also if you feel I could be doing this a better way, please feel free to show me some examples. In theory my method should have worked according to the original post. But it didn't. Your help would be most appreciated.

 

Thanks 🙂

I would need to know what the name of your yearmonth column is.   Can you provide a sample pbix?

Can't share pbix as sensitive but I can show you a screenshot of the Dim_Calendar columns if that helps? Really appreciate your support mate 🙂

sabilahmed_0-1659651495838.png

 




I would need to see the fiscal month number.  If you don't have that then use 

 

SUMMARIZE(FILTER(...),[FY],[Month])

Okay based on what you said I tried the following:


Contracted & Uncontracted = CROSSJOIN('AgreementAdditions & List',SUMMARIZE(FILTER('Dim_Calendar','Dim_Calendar'[Date] >= MIN('AgreementAdditions & List'[START_DATE]) && 'Dim_Calendar'[Date] < MAX('AgreementAdditions & List'[END_DATE])), Dim_Calendar[FY],Dim_Calendar[Month]))
 
But what I really want is a column with dates per month (first day of the month) like:
 
01/01/2021
01/02/2021
01/03/2021
 
So that when I link this column to my CALENDAR table I should get an accurate visual. With your code i get the following two columns instead:


sabilahmed_0-1659653706176.png

This method i very close to the results I want because now I have fewer rows (not millions). But instead of these columns I need dates instead.

I've got it!

 

I added a STARTOFMONTH column in the Dim_Calendar and added that to the SUMMARIZE argument.

 

I will accept your answer as the solution though. Thanks for your help 🙂

 

Cheers

In that case you don't need the year and month any more.

The fiscal month number is the column Month_SortOrder which represents the number of the month according to the fiscal year. 

 

Would it be possible to ammend my following code for me please (in full). I'm not quite understanding your code.

Contracted & Uncontracted = CROSSJOIN('AgreementAdditions & List',FILTER('Dim_Calendar','Dim_Calendar'[Date] >= MIN('AgreementAdditions & List'[START_DATE]) && 'Dim_Calendar'[Date] < MAX('AgreementAdditions & List'[END_DATE])))

 

lbendlin
Super User
Super User

use SUMMARIZE or GROUPBY.  Show your code.

Contracted & Uncontracted = CROSSJOIN('AgreementAdditions & List',FILTER('Dim_Calendar','Dim_Calendar'[Date] >= MIN('AgreementAdditions & List'[START_DATE]) && 'Dim_Calendar'[Date] < MAX('AgreementAdditions & List'[END_DATE])))

 Here's my DAX. How would I add the GROUPBY or SUMMARIZE layer?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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