Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |