Sum of days within a specific period

[Kostas]

Hello Everyone, 

I am currently having an issue. 

I have in a table a column that shows a business function, a column that have the date of when the specific funtion starts and a column that shows when it ends. I want within a visual (Column chart) to show how many total days the functions are running in a specific day. 

for example the axis will have dates (1/1/2020 - 31/12/2020) and the column above the March will counting the total days that the business function run until then. If a funtion started on 15/2 and another on 19/2, in the column that is in the axis 02/03/2019 the count will be 17 days from the first business function and 13 days from the second one so total 30!

I am sorry I know that is complicated what I need and I am not 100% sure that it makes sense the way I wrote it. Let me know if you need examples.

Thank you in advance

[sturlaws]

Hi,

using the information you provided, I set up a report, with a table with a business functions column and a start date column, as well as a start date column. I also added a date table, without any relationship to the other table.

I then created this measure

Number of days = 
VAR _currentDate =
    SELECTEDVALUE ( vDate[Date] )
RETURN
    COUNTROWS (
        GENERATE (
            vTable;
            VAR _start =
                CALCULATE ( SELECTEDVALUE ( vTable[Start Date] ) )
            RETURN
                GENERATESERIES ( _start; _currentDate; 1 )
        )
    )

br,
S

[sturlaws]

he, made that previous measure a bit more complicated than necessary, this will do:

Number of days 2 =
VAR _t =
    SELECTEDVALUE ( vDate[Date] )
RETURN
    SUMX (
        FILTER ( vTable; vTable[Start Date] <= _t );
        DATEDIFF ( vTable[Start Date]; _t; DAY ) + 1
    )

[Kostas]

Hello @sturlaws  and thank you for your response, 

Unfortunately it does not work as it is shows always a constant number of days. Also within the code where I should imput the distict audit function that runs into the specific period?

kind Regards

[sturlaws]

The way I tested it, it did not create constant number of day.

Perhaps you could share your report, or create a relevant sample report and share it?

[Kostas]

Hello, 

So until now I have a graph that shows how many active distinct business functions I have by using the measure below:

With that measure I am creating the following graph and the raw data looks like the image below. 

I want now with a similar if not the same graph to show within the date (e.g. 15/02/2019) the total days of the functions that are running until then from the date they started. 

I hope that helps

Measure =
CALCULATE (
    DISTINCTCOUNT ( Table1[Function] ),
    FILTER (
        Table1,
        NOT (
            Table1[Start Date] > MAX ( Calendar[Date] )
                || Table1[End Date] < MIN ( Calendar[Date] )
        )
    )
)

[sturlaws]

What do you mean by 'within a certain date'? Do you mean at a particulate date?

Only minor changes necessary to account for end date as well:

Measure =
VAR _t =
    SELECTEDVALUE ( vDate[Date] )
RETURN
    SUMX (
        FILTER ( vTable; vTable[Start Date] <= _t && vTable[End date] >= _t );
        DATEDIFF ( vTable[Start Date]; _t; DAY ) + 1
    )

report

[Kostas]

Hey @sturlaws 

I think now it worked. thank you very much for your help.

Only one more question if you have any ideas... some of the business functions are duplicate in the report e.g. the F9 exist twice (with the same start and end day). 

Is there a way to show the total days by taking into account only function ( as distinct) 

Thank you in advance. 

[sturlaws · Accepted solution]

if the rows are identical you write the measure like this

Measure =
VAR _date =
    SELECTEDVALUE ( vDate[Date] )
VAR _unique =
    DISTINCT ( vTable )
RETURN
    SUMX (
        FILTER ( _unique; [Start Date] <= _date && [End date] >= _date );
        DATEDIFF ( [Start Date]; _date; DAY ) + 1
    )