Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Dear Team - I am trying to calculate each four Quarter Rolling Average which include 0 value as my formulas does not works well.
Its excluding zero value and calculate measure. But i need to include to find each four Quarter Rolling Average.
Measure and example attached here.
Please guide me.
Hi,
I try to get each rolling 4 Quarter Average incase particular quarter no data found in Table then it should consider as Zero for the specific quarter and calculate average and tried the multiple way of formuls as doesn't works. shared the file in Google drive in sample file and i don't know how to share other way around. Thanks!
Hi Team,
We need to open PBIX File then hope we understand better as Average for 4 Quarter not working few yellow highlighed periods in the measure.
I've uploaded PBIX File into Google Drive and don't know how to share the file other way.
Thanks!
Hi @RajK2 ,
Your measure works fine on my end. Are those zeroes actually zeroes or they're blank because your fact table doesn't have data in those quarters/those quarters do not exist in your fact table?
Proud to be a Super User!
absolutlely you're right.. My fact table doesn't have value on specific date then my 4 Quarter average doesn't calculate as expected.
Example measurement takes where we don't have value in Fact Table then it takes SUM of Total / 4 as average (wrong answer) = 34,637
How can we fix this. pls suggest
My fact table doesn't have data in those quarters so i assign as AOP = Blank then 0 else value
Hi @RajK2 ,
Please do this in the power query, as the measure is not an actual column of data and is only calculated when used.
Hope it helps!
Best regards,
Community Support Team_ Scott Chang
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Hi Scott - I tried power Query first to fix this as different came out then we developed in Measure to get a solution.
Hi,
Does this measure work?
Measure = averagex(summarize(calculatetable(Calendar,datesbetween(calendar[date],edate(min(calendar[date]),-3),max(calendar[date]))),Calendar[Year-Qtr],"A",[AOP$]),[A])
Hi Ashish - I tried the formula as doesn't works and result as follows..
Hi,
Share the download link of the PBI file. In that show the problem very clearly.
Hello,
Question - 1 = Table 2 DAX Formula
Date : 30-09-2022 Average 4Q Wrong (highlighed)
Date : 30-09-2023 Average 4Q Wrong (highlighed)
Question - 2 = Table 1 & Table 2
Example : I used 3 years data then 12 Quarter should be fixed incase no data then zero value in 'date' and all other columns
Both tables 12 rows fixed.. You can check while changing filter
PBIX File:
https://drive.google.com/file/d/1rcyzr51WAq74yNUbIV81CjPqhBlYYyAN/view?usp=drivesdk
I do not understand your question at all. Furthermore, the link takes me to a sign-in page.
Hi Ashish - I shared PBIX File in Google drive and don't know how to share other way around.
Highlighted blue color portion i'am trying to reach in Power BI. Thanks!
On April 19, 2024, i had mentioend to you that "the link takes me to a sign-in page". Simply share the link from where i can download the file.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
86 | |
85 | |
68 | |
67 | |
63 |
User | Count |
---|---|
210 | |
118 | |
116 | |
81 | |
74 |