Allow reference date in Datesinperiod to be used dynamically across multiple date fields

[codyraptor]

I have a requirement to create a metric using datesinperiod.  I've tested it and it works fine against a single date hard coded as a reference..example Max(cxldate.date).   I would like the metric to work if the user chooses a different date...Max(acctdate.date).   What is the best/more efficient approach to making the 'reference date' variable dynamic depending on which date field is being used in a visual?  Basically, I need the reference date to be dynamic so the metric works across multilple date fields.  

Much appreciated!

[v-nuoc-msft · Accepted solution]

Hi @codyraptor 

For your question, here is the method I provided:

Here's some dummy data

"Table"

If you want to dynamically select the maximum date and calculate it, try the following. Create a measure:

Measure = 
    var max_date = 
        IF(
            ISFILTERED('Table'[Date]), 
            max('Table'[Date]), 
            BLANK()
        )
RETURN 
    CALCULATE(
        SUM('Table'[Values]), 
        DATESINPERIOD(
            'Table'[Date], 
            max_date, 
            -1, 
            MONTH
        )
    )

Here is the result

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[codyraptor]

I think I tried that solution but maybe I missed something.  What happens if they filter the year or month rather than the 'date' field.   Will that still work?

[v-nuoc-msft]

Hi @codyraptor 

The DATESINPERIOD function is a DAX function that returns a single-column date table containing a specified start date and date interval. The date format allowed by this function is the ISO 8601 standard, which is yyyy-MM-dd or yyyy-MM-ddTHH:mm:ss.

If you want to filter based on month, try the following:

Measure 2 = 
    var max_day = 
        IF(
            ISFILTERED('Table'[Date]), 
            MAX('Table'[Date]), 
            BLANK()
        )
RETURN  
    CALCULATE(
        sum('Table'[Values]), 
        FILTER(
            ALL('Table'), 
            MONTH('Table'[Date]) = MONTH(max_day)
        )
    )

Here is the result.

Please see page 2 of the attachment.

If you're still having problems, provide some dummy data and the desired outcome. It is best presented in the form of a table.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.