Forum Discussion

codyraptor's avatar
codyraptor
Icon for Resolver I rankResolver I
2 years ago
Solved

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

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!

datesinperiod
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      codyraptor
      Icon for Resolver I rankResolver I
      2 years ago

      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?

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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.