DAX measure to calculate dates dynamically

[Anonymous]

Hello All,

I currently have a situation whereby I need to measure daily performance of a program. The basic formula is to divide the total performance by individual locations by the number of active days. This gets much more complicated because the active days are different per location where I have inactive periods per location. I am able to do this on a year to date whereby I measure the total number of active days per location (calculate the total number of days in the year and then subtract the inactive days), but when I filter the denominator of days remains the same. The table I have looks something like this:

Location	Start	End	Days Active
A	January 31st	March 7th	(44 days total - 35 days inactive) = 9 days (between January 31st and March 15th)
B	Ditto	March 6th	(44 days total - 34 days inactive) = 10 days (between January 31st and March 15th)

[v-shex-msft]

Hi @Anonymous,

I'm not so sure how did the inactive date calculates, can you please explain more about your scenario?

How to Get Your Question Answered Quickly

Regards,

Xiaoxin Sheng

[Anonymous · Accepted solution]

Hello, 

Actually, I was able to solve my problem! The solution has 2 parts:

1. in power Query, I added a date column for "latest" and modified the M to pick the latest of 3 dates: Install date, open date, or fiscal year start. This becomes my effective date

2. write the dax measure using the sumx function on the table and I use the datediff function in the expression where, if the min of the filtered date is before the effective date I use effective date (otherwise I use the min date) and if the max of the of the filtered date is before the effective date I use the effective date.

this works for me, and I'm suspecting I've got a relationship I need to change as it might be filtering out some assets when I filter, but this will work for me.

[v-shex-msft]

Hi @Anonymous,

I'm glad to hear you find out the solutions.🙂

BTW, you can also take a look at the following steps if they help your scenario.

1. Extract the calendar date ranges that include the start and end and store them into the variable.
2. Collect not matched date ranges from your table(e.g. holiday, weekend, other leave dates, you can use union function to merge them) and use the 'except' function to exclude them from your date ranges.
3. Use 'countrows' function to calculate the remaining dates and they should suitable for your requirement.

Sample:

Measure =
VAR _range =
    //generate the calendar of start end date and exclude weekends
    FILTER (
        GENERATESERIES ( 'start', 'end' ),
        WEEKDAY ( [Value], 2 ) <= 5
    )
VAR _holiday =
    //extract holiday
    CALCULATETABLE (
        VALUES ( table[Date] ),
        FILTER ( table, 'conditons' )
    )
VAR _leave =
    //other type of leave dates
    CALCULATETABLE (
        VALUES ( table2[Date] ),
        FILTER ( table2, 'conditons' )
    )
RETURN
    COUNTROWS ( EXCEPT ( _range, UNION ( _holiday, _leave ) ) )

Regards,

Xiaoxin Sheng