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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Jadegirlify
Helper I
Helper I

Date difference

I have a table in powerbi has a thousands of date rows.

Start date column of 1/23/2024 and End  date column of 4/15/2024.

How can I create January, February and March columns based on the start and end date columns to show that January has a days count value of 9, February has a days count value of 29 and March has a days count value of 31, and April has 15 days?

Thanks 

1 ACCEPTED SOLUTION

@Jadegirlify Oh, I understand what you want now after reading a bit closer. This is kind of similar to Hours Breakdown but for months. Should be something like:

January Column = 
  VAR __Month = 1
  VAR __Calendar = 
    ADDCOLUMN(
      CALENDAR( [Start Date], [End Date]),
      __Month = MONTH( [Date] )
    )
  VAR __Result = COUNTROWS( FILTER( __Calendar, [__Month] = __Month ) )
RETURN
  __Result

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

3 REPLIES 3
Greg_Deckler
Super User
Super User

@Jadegirlify So generally you would create two columns, Month = FORMAT([Date], "mmmm") and MonthSort = MONTH([Date]). And then set the sort by column for Month to MonthSort.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler Thanks. That didn't work. The result table should look like this. See attached. 

IMG_0203.jpeg

@Jadegirlify Oh, I understand what you want now after reading a bit closer. This is kind of similar to Hours Breakdown but for months. Should be something like:

January Column = 
  VAR __Month = 1
  VAR __Calendar = 
    ADDCOLUMN(
      CALENDAR( [Start Date], [End Date]),
      __Month = MONTH( [Date] )
    )
  VAR __Result = COUNTROWS( FILTER( __Calendar, [__Month] = __Month ) )
RETURN
  __Result

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors