Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Folks, could you please assist me with this?
I have very messy data, which contains 3 columns:
1) FA
2) VERSION
3) SUM(AMOUNT)
4) FSI (=financial statement item number)
what makes things so hard are the following rules:
If FA is an integer (a number), then SUM(AMOUNT) is $$$
If FA is a string (some words), then SUM(AMOUNT) is fte's (so it refers to people, and not $$$)
If VERSION = CCA, then SUM(AMOUNT) is refering to Actual numbers. IF VERSION is everything else, then SUM(AMOUNT) refers to Budget / Forecast / Rolling forecast / actuals from a different system / anything else but Actuals.
I need to calculate the 6 months Rolling Average for Actual $$$ (so, FA = a number & VERSION = CCA). In addition, the rolling average needs to be per FSI.
I do have a DATE calculated measure: DATE = date([fiscal_year],[fiscal_month],01)
I'm rather noob and don't understand DAX good enough yet to figure this one out. (so suggestions on where to learn DAX are also most welcome). Halp plz?
edit: Just made a calculated measure called CCA_$. would that help?
Solved! Go to Solution.
Hi @Olia,
Based on my test, you could refer to below formula to calculate a table meets the requirements that 'FA is a number' and VERSION=CCA':
Table = CALCULATETABLE('Sheet2',FILTER(Sheet2,'Sheet2'[VERSION]="CCA"&&UNICODE('Sheet2'[FA])<65))
Result:
But I could not understand how do you want to calculate the average Actual $$$ per FSI? Could you please offer me more information if possible? You could also download the pbix file to have a view.
Regards,
Daniel He
Hi @Olia,
Could you please post me some sample data to have a test and post your desired result if possible?
Regards,
Daniel He
Hi Daniel,
Yes, sorry.
link valid for 7d
Hi @Olia,
Based on my test, you could refer to below formula to calculate a table meets the requirements that 'FA is a number' and VERSION=CCA':
Table = CALCULATETABLE('Sheet2',FILTER(Sheet2,'Sheet2'[VERSION]="CCA"&&UNICODE('Sheet2'[FA])<65))
Result:
But I could not understand how do you want to calculate the average Actual $$$ per FSI? Could you please offer me more information if possible? You could also download the pbix file to have a view.
Regards,
Daniel He