Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
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]
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.
@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
User | Count |
---|---|
98 | |
90 | |
78 | |
71 | |
64 |
User | Count |
---|---|
114 | |
97 | |
95 | |
68 | |
65 |