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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

Count the days in each month from two dates

Hi !!

 

So, my problem now is that I need to calculate the number of days in each month from one column of start date and another column with end date. I'm a new power bi user, so I don't know I should create a column or a measure to solve this...

 

Example (Dates in format dd/mm/aaaa):

Start DateEnd Date
10/03/202021/01/2021

 

So in March/20 it should be 22 days , April/20 30 days...and so it goes! lol

 

Thanks in advance!!

1 ACCEPTED SOLUTION
v-alq-msft
Community Support
Community Support

Hi, @Anonymous 

 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

a1.png

 

Calendar(a calculated table):

Calendar = 
CALENDAR(
    DATE(2020,3,1),
    DATE(2021,1,31)
)

 

You may create a measure as below.

Count Days = 
var _enddate = 
CALCULATE(
    MAX('Table'[End Date]),
    ALL('Table')
)
var _startdate = 
CALCULATE(
    MAX('Table'[Start Date]),
    ALL('Table')
)
return
COUNTROWS(
    FILTER(
        'Calendar',
        [Date]<=_enddate&&
        [Date]>=_startdate
    )
)

 

Result:

a2.png

 

Best Regards

Allan

 

If this post helps,then consider Accepting it as the solution to help other members find it faster.

 

View solution in original post

6 REPLIES 6
v-alq-msft
Community Support
Community Support

Hi, @Anonymous 

 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

a1.png

 

Calendar(a calculated table):

Calendar = 
CALENDAR(
    DATE(2020,3,1),
    DATE(2021,1,31)
)

 

You may create a measure as below.

Count Days = 
var _enddate = 
CALCULATE(
    MAX('Table'[End Date]),
    ALL('Table')
)
var _startdate = 
CALCULATE(
    MAX('Table'[Start Date]),
    ALL('Table')
)
return
COUNTROWS(
    FILTER(
        'Calendar',
        [Date]<=_enddate&&
        [Date]>=_startdate
    )
)

 

Result:

a2.png

 

Best Regards

Allan

 

If this post helps,then consider Accepting it as the solution to help other members find it faster.

 

amitchandak
Super User
Super User

@Anonymous , refer this file on similar problem will help you

https://www.dropbox.com/s/bqbei7b8qbq5xez/leavebetweendates.pbix?dl=0

Anonymous
Not applicable

Hi @

 

I tried to access the link you sent me, but apparently it is no longer available....

 

Thank you!

 

 

@Anonymous - Are you still haveing an issue with this? Seems like some solid solutions have been proposed. If you are still having issues, need to understand what the actual output will be, a measure or a calculated column or ?

 


@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
vivran22
Community Champion
Community Champion

Hello @Anonymous

 

There are multiple ways to achieve this in Power BI. 

 

First we need to decide whether we need the days difference in a column or as a measure. You may find following helpful:

When to use a Calculated Column?

Use calculated column when:

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.