cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lfrigione
Helper I
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

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.

 

date_difference.gif

 

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

23 REPLIES 23
MFelix
Super User III
Super User III

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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

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.

 

In order to help you and picking up your example.

 

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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

Hi,

 

You may refer to my solution in this file.

 

Hope this helps.

 

Untitled.png


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

Yes but I have two fields.

How can I Explode every period?

And... and would it be performing?

Thank you @Ashish_Mathur

Hi @lfrigione,

 

My solution gives the correct answer.  There are 3 days between Jan 1 and Jan 3.

 

Untitled.png


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

Yes but i didn't have one field representing the day of the period.

I have a StartDate and EndDate field so I can't use your solution.

Can I explode every period in days?

thank you @Ashish_Mathur,

lfrigione.

Hi,

 

That is exactly what my solution does.  It expands the Start Date and End date into a single column.  Look at the query Editor to know the technique i have adopted to expand two columns into a single column.


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

Hi @lfrigione,

 

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

 

Then add this measure:

 

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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

Thank you for your help,

lfrigione

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



It not work, I'm thinking for a solution

 

These are my testing data:

image.png

 

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

 

My relationships:

image.png

image.png

My report:

 

image.png

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

Hi @lfrigione,

 

Sorry for the delay, I was abble to solve the issue:

 

- Create Date table (already in your model) - Don't make any relationship with other tables

- Add the following measure

 

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
                )
                    + 1
            )
        );
        [DAYS]
    )

Should give the expected result. Be aware that my slicer is based on the dates and not on months, please check if it is ok like this.

 

Count_days.gif

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



It doesn't work

In the case of StartDate = 1/1/2017 and EndDate = 3/1/2017 It returns 10, as I have seen in the gif, but the only period that contains the subperiod 1/1/2017 - 3/1/2017 is the first and the formula has to return 2

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.

 

date_difference.gif

 

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

Sorry, the formula work and It is a grate think but remains two big problems.

I can't work with months and the filter does not filter FactTable for further analisis in other graphs.

Thank you very much @MFelix

Hi @lfrigione,

 

Regarding the questions with months you just need to choose the field month from the hierarchy on the DimDate field:

 

mONTH.png

Or create a column getting the month name and then use it as your slicer.

 

Regarding the other charts, taking into account that you need to have a single selection on the end date probably the best way is to make the measures based on the inactive relationship between Start Date and End Date with the Calendar table and use the USERRELATIOSHIP and use those measures on your charts.

 

Regards,

MFelix 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Thank you very much @MFelix, I'm going to try.

How can I see the date hierarchy in fields dashboard?

 

Hi @lfrigione,

 

You need to go on the latest version of PBI (January 2018) and activate the preview feature (link). You can also make the several columns of the Year, Quarter, Month, Day and create your own hierarchy.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors