cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Super User III
Super User III

Re: Counting days in a period

Hi @lfrigione,

 

Due work schedulle, I was not abble to give you an answer yet.

 

Wil try to work on this ASAP and return back to you.

 

Sorry for the delay.

 

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





Highlighted
Super User III
Super User III

Re: Counting days in a period

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





Highlighted
Super User IV
Super User IV

Re: Counting days in a period

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/
Highlighted
Helper I
Helper I

Re: Counting days in a period

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

Highlighted
Helper I
Helper I

Re: Counting days in a period

Yes but I have two fields.

How can I Explode every period?

And... and would it be performing?

Thank you @Ashish_Mathur

Highlighted
Super User III
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.

 

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

Highlighted
Helper I
Helper I

Re: Counting days in a period

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

Highlighted
Super User III
Super User III

Re: Counting days in a period

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





Highlighted
Super User IV
Super User IV

Re: Counting days in a period

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/
Highlighted
Helper I
Helper I

Re: Counting days in a period

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.

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

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

Community Blog

Community Blog

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

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Community Summit Australia – Join Online!

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