cancel
Showing results for
Did you mean:
Highlighted
Helper I

## Counting days in a period

I have several records with a StartDate and an EndDate of a period.

I need to calculate days between this dates.

The real problem is in filtering the data.

For example If i have a StartDate = DATE(2018,1,1) and an EndDate(2018,2,2) and I would like to filter the period in February, the formula have to return two days

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III

## Re: Counting days in a period

Hi @lfrigione,

Just to clarify one thing, in the GIF beware that the dates are in US format (an error of PBI that is being solved), so the dates from 1/1/2017 - 3/1/2017 is January 1st to March 1st in this case is 10 days.

If you change the slicer to January 1st to January 3rd you get 3 days because in my formula I added +1 didn't know if you want the datediff result or the count of all days, just make the formula like this:

```Counting_days =
VAR START_DATE =
MIN ( DimDate[Date] )
VAR END_DATE =
MAX ( DimDate[Date] )
RETURN
SUMX (
SUMMARIZE (
FactTable;
FactTable[StartDate];
FactTable[EndDate];
"DAYS"; IF (
FactTable[StartDate] < START_DATE
&& FactTable[EndDate] < START_DATE
|| FactTable[StartDate] > END_DATE
&& FactTable[EndDate] > END_DATE;
0;
DATEDIFF (
IF ( FactTable[StartDate] < START_DATE; START_DATE; FactTable[StartDate] );
IF ( FactTable[EndDate] > END_DATE; END_DATE; FactTable[EndDate] );
DAY
)
)
);
[DAYS]
)```

But be aware that if you get the period from 1 January to 1 January de days difference is 0 and not 1.

See the PBIX sample with the formula above working and try to check if this is the resul you want.

Regards,

MFelix

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog:

Power BI em Português

23 REPLIES 23
Highlighted
Super User III

## Re: Counting days in a period

Hi @lfrigione,

Try to use the DATEDIFF formula the last variable on the formula is the period check this link

Regards,

MFelix

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog:

Power BI em Português

Highlighted
Helper I

## Re: Counting days in a period

The problem with DATEDIFF is that:

in a filter i can put a record field that in this case may be the StartDate or the EndDate,

if one of this two dates don't match the filter it trunkate all the period without counting the days that match the filter

Highlighted
Super User III

## Re: Counting days in a period

Hi @lfrigione,

When you have a filter you need to place make some small changes to your calculations in order to have it well made.

If you select in your filter only the date of 01/02/2018 as end date should your calculation be made as 01/02/2018 to 01/02/2018 so 0 days or should it give something else.

Can you elaborate with different examples of dates so I can pin point exactly how the measure you need can be better writen.

Regards,

MFelix

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog:

Power BI em Português

Highlighted
Helper I

## Re: Counting days in a period

I will try to explain it better.

I have a table with a lot of fields but in this moment we focus on the StartDate and EndDate fields.

I have something like:

"gg-mm-yyyy"

StartDate               EndDate

01/01/2016           01/02/2016

01/01/2017           02/02/2017

I need a formula that can sum all the days in every period, something like this: SUMX(datesSheet, DATEDIFF(StartDate, EndDate))

The problem is that i need also a filter that allow me to select a "subperiod".

It means that without the filter the formula return me 2 + 33

With the filter setted on February for example the formula have to trunkate the initial periods only in the period of February and then return me the total number of days:  2.

If I select December it will return 0.

Thank you very much for helping me @MFelix

Highlighted
Super User III

## Re: Counting days in a period

Hi @lfrigione,

Create a calendar table and make an active relationship with Start Date and a inactive relationship with the End Date

```Countdays =
VAR start_date =
MIN ( DimDate[Date] )
VAR end_date =
MAX ( DimDate[Date] )
RETURN
CALCULATE (
SUMX (
FactTable;
DATEDIFF (
IF ( FactTable[StartDate] <= start_date; start_date; FactTable[StartDate] );
IF ( FactTable[EndDate] >= end_date; end_date; FactTable[EndDate] );
DAY
)
);
USERELATIONSHIP ( DimDate[Date]; FactTable[EndDate] )
)
```

This should give the expected result.

Please tell me if it works or if there are any questions.

Regards,

MFelix

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog:

Power BI em Português

Highlighted
Helper I

## Re: Counting days in a period

This formula have a little problem:

If I have a period like: from 15-01-2017 to 15-03-2017 It exlude all the days of February

lfrigione

Highlighted
Super User III

## Re: Counting days in a period

Hi @lfrigione,

The question was related with the way the formula as including/ excluding the end dates remove the relationships between date tables and Date columns and add the following measure:

```Countdays =
VAR start_date =
MIN ( DimDate[Date] )
VAR end_date =
MAX ( DimDate[Date] )
VAR counting_days =
CALCULATE (
SUMX (
FactTable;
DATEDIFF (
IF ( FactTable[StartDate] <= start_date; start_date; FactTable[StartDate] );
IF ( FactTable[EndDate] >= end_date; end_date; FactTable[EndDate] );
DAY
)
)
)
+ 1
RETURN
IF ( counting_days < 0; 0; counting_days )```

Should work as expected.

Regards,

MFelix

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog:

Power BI em Português

Highlighted
Helper I

## Re: Counting days in a period

It not work, I'm thinking for a solution

These are my testing data:

The calendar table is calculated with this formula: Calendar = CALENDAR(DATE(2000;1;1); DATE(2025;12;31))

My relationships:

My report:

If I select March It return me 1 that is impossible, It have to return 15.

Highlighted
Helper I

## Re: Counting days in a period

I have an Idea,

Can I create a calculated colum in the calendar that represent the number of periods that contains the day?.

For every record it have to calcuate how many periods meet this condition: FactTable[StartDate] <= CurrentDay <= FactTable[EndDate]

and save this information or something like that.

Thanks again,

lfrigione.

Announcements

#### August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

#### Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

#### Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors