Rolling average with conditions

[Olia]

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?

[v-danhe-msft]

Hi @Olia,

Could you please post me some sample data to have a test and post your desired result if possible?

Regards,

Daniel He

[Olia]

Hi Daniel,

Yes, sorry.

https://wetransfer.com/downloads/8b6ae403a4a8dce7bcbf876418eb831e20180925080729/53553ff87a9877b74e22...

link valid for 7d

[v-danhe-msft · Accepted 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