Number of days

[Anonymous]

Hi,

I have 2 sets of dates in my dataset, event start date and event end date. I need to create a visual which shows the number of days in a month the event has been active. For example if the start date is 10/12/2021 and end date is 11/2/2022 for a particular event, then in a month by month visual it should show as 

year   month   number of days

2021   12             21

2022    1              31

2022    2              11

Appreciate anyhelp.

Thanks

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

I am not sure how your data model looks like, but I tried to create a sample pbix file like below.

Number of Days: =
CALCULATE (
    COUNTROWS ( 'Calendar' ),
    FILTER (
        'Calendar',
        'Calendar'[Date] >= MAX ( Events[StartDate] )
            && 'Calendar'[Date] <= MAX ( Events[EndDate] )
    )
)

[Anonymous]

Hi Jihwan,

Below is what I got from the solution you provided.

As you can see I have these measures running in 2 different tables. Now I need to compare the results. As you can see one starts from Mar 2020 and other from Jan 2017. How would I limit the second table to show only the results from March 2020, ie filter the Month year from one table to only have values that other one has.

Thanks in Advance

[Jihwan_Kim]

Hi,

Thank you for your feedback.

I am not sure if I understood your question correctly, but please try to include the condition in the measure. I am not sure if the below works for your data model because I do not know your data model, but please try.

Number of Days V2: =
IF (
    MIN ( 'Calendar'[Date] ) >= DATE ( 2020, 03, 01 ),
    CALCULATE (
        COUNTROWS ( 'Calendar' ),
        FILTER (
            'Calendar',
            'Calendar'[Date] >= MAX ( Events[StartDate] )
                && 'Calendar'[Date] <= MAX ( Events[EndDate] )
        )
    )
)

[Anonymous]

Thanks again, this works well if the dates are continuous. For example lets say I have these set of dates 

Here ideally it should work from March 2014 to Sept 2014 and then from 10/07/2021 to 29/08/2021 and then again from 10/02/2022 to present date (1/01/9998 represents future or ongoing)

CALCULATE (

COUNTROWS ( 'Date' ),

FILTER (

'Date',

'Date'[Date] >= MIN ( xyz[_start] )

&& 'Date'[Date] <= MAX ( xyz[_end] )

)

)
cannot do this as it finds the max and min and assumes its continuous. Whats the work around?
Thanks again