Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
Hi,
I have a requirement to fill the missing values of the months with the average of previous months with in fiscal year,
Note: My fiscal year starts from Oct so the average should be calculated form oct of that year.
Can some one help me to achieve this. Thanks in advance.
Solved! Go to Solution.
Hi @Reddy5833 ,
Please create two calculated columns:
Year =
VAR _start_of_year =
STARTOFYEAR ( 'Date'[Date], "9/30" )
VAR _fin_year =
YEAR ( _start_of_year )
RETURN
_fin_year
Column =
VAR _avg =
CALCULATE (
AVERAGE ( 'Table'[Value] ),
FILTER (
'Table',
'Table'[Category] = EARLIER ( 'Table'[Category] )
&& 'Table'[Year] = EARLIER ( 'Table'[Year] )
)
)
VAR _fill =
IF ( 'Table'[Value] <> BLANK (), 'Table'[Value], _avg )
RETURN
_fill
Result:
The PBIX file is attached for reference.
Best Regards,
Gao
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data
Hi @Reddy5833 ,
Please create two calculated columns:
Year =
VAR _start_of_year =
STARTOFYEAR ( 'Date'[Date], "9/30" )
VAR _fin_year =
YEAR ( _start_of_year )
RETURN
_fin_year
Column =
VAR _avg =
CALCULATE (
AVERAGE ( 'Table'[Value] ),
FILTER (
'Table',
'Table'[Category] = EARLIER ( 'Table'[Category] )
&& 'Table'[Year] = EARLIER ( 'Table'[Year] )
)
)
VAR _fill =
IF ( 'Table'[Value] <> BLANK (), 'Table'[Value], _avg )
RETURN
_fill
Result:
The PBIX file is attached for reference.
Best Regards,
Gao
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
User | Count |
---|---|
94 | |
90 | |
83 | |
76 | |
49 |
User | Count |
---|---|
145 | |
140 | |
109 | |
68 | |
55 |