Count the days in each month from two dates

[Anonymous]

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 Date	End Date
10/03/2020	21/01/2021

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

Thanks in advance!!

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

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

Table:

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:

Best Regards

Allan

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

[amitchandak]

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

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

[Anonymous]

Hi @amitchandak!

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

Thank you!

[Greg_Deckler]

@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 ?

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[vivran22]

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:

• we use that column as a filter or slicer,

• use the field in the Column or Row section of a Pivot Table or matrix

• categorize the results. For example, age between 10 & 20, 20 & 30, and so on.

  For everything else, we create measures.

  Power Query should be the first choice to create calculated columns and categories as it is designed to handle such calculations more efficiently.

  Related article:

  https://www.vivran.in/post/say-hello-to-dax

  If you need it as a column, then in Power Query, use the following steps:

  Select End Date & Start Date by holding down the CTRL key(in that order) > Add Column > Date > Subtract Days

  In Power BI, we can add a calculated column using DAX:

  

  As a measure, you may use the following formula:

  

  Hope this answers your query.

  Cheers!
  Vivek
  
  Blog: vivran.in/my-blog
  Connect on LinkedIn
  Follow on Twitter