distribution of the number of days per month between 2 dates

[Cobra77]

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.

[Anonymous]

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]

Disconnected Calendar table and this measure:

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

[Cobra77]

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]

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? 

[Cobra77]

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 · Accepted solution]

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])

[Anonymous]

@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

[hpatel247]

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]

@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

[hpatel247]

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:

Hope this helps

kind regards

Hetal

[Stachu]

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?

[hpatel247]

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

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

[Cobra77]

Perfect @Stachu,

Thanks

[Stachu]

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)