Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Cobra77
Post Patron
Post Patron

distribution of the number of days per month between 2 dates

Hi ,

 

Sample Data with 1 line :

DateBegin                  DateEnd

2018-05-21               2018-08-15

 

we would like a graph with the distribution by month of the number of days :

10 for may

30 for june

31 for july

15 for august   

 

It is possible dynamically ?  by a measure ?

 

thanks.

1 ACCEPTED SOLUTION
Stachu
Community Champion
Community Champion

try this

Measure = 
VAR AllPossibleDates = GENERATE('Table','Calendar')
VAR FlagDates = FILTER(ADDCOLUMNS(AllPossibleDates,"Flag",IF([Date]>[DateBegin] && [Date]<=[DateEnd],1,0)),[Flag]=1)
RETURN
SUMX(FlagDates,[Flag])


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

View solution in original post

14 REPLIES 14
Anonymous
Not applicable

Hi,

 

I implement the measure and it works perfectly. But my issue is  I need the last year value. 

I tried using SAMEPERIODLASTYEAR it left blank, since the date table and the fact table is not connected.

Do you maybe have any solution for this?

Thank you,

Greg_Deckler
Super User
Super User

Disconnected Calendar table and this measure:

 

Measure 6 = CALCULATE(COUNT('Calendar'[Date]),FILTER('Calendar',[Date]>=MAX(Table13[DateBegin])&&[Date]<=MAX(Table13[DateEnd])))

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

hi @Greg_Deckler@Stachu,

thanks for your answer,

 

but if I have multiple lines , i can't use Max

 

example of people missing over several days :

Name       DateBegin                  DateEnd

User 1      2018-05-21               2018-08-15        

User 2      2018-06-18               2018-07-10 

User 3      2018-07-15               2018-09-10

 

the result by month : 

10 (user1 only )                                  =10 for may

30 (user1)  + 12 (user2)                      = 42 for june

31 (user1) + 10 (user2) + 16 (user3)   =  57 for july

15 (user1) + 31 (user3)                       = 46 for august

10 (user3)                                           = 10 for september   

 

and with millions of lines potentially

 

Thanks for your help

best regards

 

Stachu
Community Champion
Community Champion

for a single user selected it will calculate max for that user's filter context (one row), so the code is still valid

how do you want it to behave when multiple users are selected? 



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

we want to have a globlale vision, and to be able to filter by user or other filters (not put in the example, example by sector, division etc ...)

Stachu
Community Champion
Community Champion

try this

Measure = 
VAR AllPossibleDates = GENERATE('Table','Calendar')
VAR FlagDates = FILTER(ADDCOLUMNS(AllPossibleDates,"Flag",IF([Date]>[DateBegin] && [Date]<=[DateEnd],1,0)),[Flag]=1)
RETURN
SUMX(FlagDates,[Flag])


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

@Stachu HI, I try your measure, and its works for almost all the months, but not for 1 of it, do you have any idea of why this could happen?

thanks you

Hi @Stachu,

 

I know this is an old post that i am responding to but i am trying to do something similar.

 

I have used the measure you have provided but not sure how exactly it should work. I was expecting this to calculate the number of days used per month.

 

I.e a client has start date of 16/04/2018 and end date of 29/08/2018.

 

Therefore i would expect this to be displayed as follows:

 

Apr - 14 Days

May - 31 Days

Jun - 30 Days

Jul - 31 Days

Aug - 29 days

 

But this does not work and shows the total days in April and then the total which would then add up May-June . Is there a way to calculate the number of days per month or will this not work because it cannot populate future months if that makes sense?

 

kind regards

 

Hetal

Stachu
Community Champion
Community Champion

@hpatel247
how your data model looks like?
where are the begin/end parameters coming from?
with the structures from original post the measrue will behave exactly the same way you describe it, assuming the month will be in the visual



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Hi @Stachu,

 

I have a calendar table that consists of the dates from 01/04/2018 to 31/03/2019. I have added a column that gives me the month name therefore i can add this on my visual to give me the monthly breakdown.

 

My begin/end parameters are called Period From and Period to. Most of my dates start from 01/04/2018 and if the are currently open, the Period end will show as 31/03/2019 or it will use the actual end date. There will also be some cases that are new in the year so the Period From will be when they started. Below is what it looks like using the measure you provided in the original post:

 

Sample.png

Hope this helps

 

kind regards

 

Hetal

Stachu
Community Champion
Community Champion

how do Period From and Period To relate to Sort Type? are they in the same table?
e.g. what are the values for these parameters for '5. Secure', and how does this look in a table?



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Hi @Stachu

 

Yes the Period From and Period To are in the same table as the Sort Type.

 

Below is a sample of what i would like to achieve both by client and by Sort Type

 

Sample2.png

So the top table is by client and the table below sums the total by category. You can do this in excel using =MAX(0,(MIN($M2,DATE(YEAR(Q$1),MONTH(Q$1)+1,0))-MAX($L2,Q$1)+1)) but not sure if this can be done within Power BI.

 

Appreciate any help/advise you can offer.

 

kind regards

 

Hetal

Perfect @Stachu,

 

Thanks

Stachu
Community Champion
Community Champion

you will still need Calendar table with all the dates
https://www.sqlbi.com/articles/reference-date-table-in-dax-and-power-bi/

 

but then this code will work

Measure = 
VAR StartDate = MAX('Table'[DateBegin])
VAR EndDate = MAX('Table'[DateEnd])
RETURN
CALCULATE(COUNTROWS('Calendar'),'Calendar'[Date]>StartDate,'Calendar'[Date]<=EndDate)


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.