Forum Discussion

Quenril's avatar
Quenril
Resolver I
2 years ago
Solved

Help with DATESINPERIOD

Hello,

I have a DATESINPERIOD formula I am using to calculate a rolling 12-month average however it is not returning the correct result. I have 12 measure in total for which I am calculating current month, previous month and last twelve months rolling average. In each case the current month and previous month are calculating correctly and the rolling twelve month average is not.  

 
One of my 'current month' measures is (time period selected in date slicer):
Number Client Meetings = 
DISTINCTCOUNT(
('Client Meetings'[INDEX])
)
This is working fine and returning the correct result.
 
My rolling average LTM measure is below:
Rolling average LTM Number Client Meetings =
DIVIDE(
    CALCULATE(
    [Number Client Meetings],
 DATESINPERIOD('Rolling Calendar lookup'[Date],
        MAX('Rolling Calendar lookup'[Date]),
        -12,
        MONTH)
),
12,
BLANK()
)
This is returning a result is 298 rather than 344... I cannot work out why. Please can someone help? I have the same issue with all of the measures calculating the rolling 12-month average. 
 
Thank you
Quenril
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Thanks for the reply from foodd  ,please allow me to provide another insight:

    Hi, Quenril 

    Regarding the issue you raised, my solution is as follows:

    1.First I have created the following table and the column names and data are the data you have given:

    2. Below are the measure I've created for your needs:

    avrg = 
VAR currrunt1 =
    MAX ( 'time'[Date] )
RETURN
    DIVIDE (
        CALCULATE (
            DISTINCTCOUNT ( 'Client Meetings'[Index] ),
            FILTER (
                ALLSELECTED ( 'Client Meetings' ),
                'Client Meetings'[Date] > EDATE ( currrunt1, -13 )
                    && 'Client Meetings'[Date] < currrunt1
            )
        ),
        12
    )

    3.Here's my final result, which I hope meets your requirements.

     

     

     

    Can you share sample data and sample output in tabular format if I am misunderstanding? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

     

    Please find the attached pbix relevant to the case.

     

    Best Regards,

    Leroy Lu

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

     

3 Replies

  • foodd's avatar
    foodd
    Community Champion
    2 years ago

    Hello Quenril, and thank you for sharing a question with the Community.  Please remember to adhere to the decorum of the Community Forum when asking a question.

    Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot). You can upload the PBIX file to a cloud storage service such as OneDrive, Google Drive, Dropbox, or to a Github repository, and then share a file’s URL.

    https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

    Please show the expected outcome based on the sample data you provided.

    https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523/highlight/true#M607150

    This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Thanks for the reply from foodd  ,please allow me to provide another insight:

    Hi, Quenril 

    Regarding the issue you raised, my solution is as follows:

    1.First I have created the following table and the column names and data are the data you have given:

    2. Below are the measure I've created for your needs:

    avrg = 
VAR currrunt1 =
    MAX ( 'time'[Date] )
RETURN
    DIVIDE (
        CALCULATE (
            DISTINCTCOUNT ( 'Client Meetings'[Index] ),
            FILTER (
                ALLSELECTED ( 'Client Meetings' ),
                'Client Meetings'[Date] > EDATE ( currrunt1, -13 )
                    && 'Client Meetings'[Date] < currrunt1
            )
        ),
        12
    )

    3.Here's my final result, which I hope meets your requirements.

     

     

     

    Can you share sample data and sample output in tabular format if I am misunderstanding? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

     

    Please find the attached pbix relevant to the case.

     

    Best Regards,

    Leroy Lu

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

     

    • Quenril's avatar
      Quenril
      Resolver I
      2 years ago

      Thank you so much Leroy - this is perfect. Exactly what I needed. Thank you so much for your help!

      Quenril