cancel
Showing results for
Did you mean:
Helper III

## Rolling average with conditions

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?

1 ACCEPTED SOLUTION
Microsoft

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

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Microsoft

Hi @Olia,

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

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper III
Microsoft

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

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements