Solved: Re: Counting days in a period - Page 2

lfrigione · ‎02-19-2018

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

MFelix · ‎02-26-2018

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

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