DATESINPERIOD function is not giving the right results

[Anonymous]

 Hi,

Need help with below Issue.

I have a requirement where I need to calculate the variance for last 6 months for which I am creating a measure using DATESINPERIOD function. I am enable to achieve the expected results and I couldn’t able to figure out where I am doing wrong. is there any other way to acheive this?

I am not able to attaching the sample PBIX file here.

please find it in the below link: https://drive.google.com/file/d/12mBtxOJ68JKp8Ew3bqrty7LY8buyA_Po/view?usp=drivesdk 

Thanks alot

[V-pazhen-msft]

With your pbix, I calculated the variance of the last 6 month with the EARLIER instead of DATESINPERIOD.

variance-6 =
(
    CALCULATE (
        AVERAGEX ( 'Table', 'Table'[Distance_KM] ),
        FILTER (
            FILTER (
                'Table',
                'Table'[REP_DATE].[MonthNo] <= EARLIER ( 'Table'[REP_DATE].[MonthNo] )
            ),
            'Table'[REP_DATE].[MonthNo]
                >= EARLIER ( 'Table'[REP_DATE].[MonthNo] ) - 6
        )
    ) - 'Table'[Distance_KM]
) / 'Table'[Distance_KM]

https://microsoftapc-my.sharepoint.com/:u:/g/personal/v-pazhen_microsoft_com/EbQDR6jtpzxCsavH-4ueJOQ... 

Paul
Best

[Anonymous]

Hi Paul,

Thank you very much for the response. I used EARLIER function in the same way but it is showing syntax error if I put the date column in EARLIER function.  I couldn't figure out where I was doing wrong and I am unable to access the sharepoint link in case if you are sharing the PBIX file.

[V-pazhen-msft]

@Anonymous 

Try this link: 
https://qiuyunus-my.sharepoint.com/:u:/g/personal/paul_qiuyunus_onmicrosoft_com/EXh8n9UpjwxPpkrnUWbqJsgB3XN7GnZNXZzJEb8RsvIl1Q?e=3STtKp 

You can use my solution as an idea. Because the sample may not look exactly same to yours, I correct the format for a few columns. And used Date.MonthNo. instead of the month column. 

Best,
Paul