Apply count fisrt fiscal month to all month for rest of fiscal year

[Anonymous]

Hi all,

Would someone be able to help with a problem I need solving?

I need to take the count from the 1st period of each fiscal month and apply in a column next to the proceeding months within that fiscal year.

The fiscal year starts in Apr, so I need something like looks the below

Fiscal Year  | Count | CountofFirstFiscalMonth

Apr 2017    |   7       |                 7

May 2017   |   5       |                 7

May 2017   |   9       |                 7

I've enclosed a sample data I'm working using

Tab 1 - FactData Sample

Tab 2- DimDate table Sample

Tab 3 - an example of what I'm trying to Achieve.

Sample Data =>

https://drive.google.com/file/d/10O0MDXWoBK39xFAFADh23MJ8v8_7m1EL/view?usp=sharing

Thanks indvance for assistance.

[v-lili6-msft]

hi, @Anonymous

You could try to use EARLIER Function to add a column like below:

Result = CALCULATE(MAX(Table1[Month Count]),FILTER(ALL(Table1),Table1[FiscalYear]=EARLIER(Table1[FiscalYear])&&Table1[FiscalMonth]=1))

Result:

Best Regards,

Lin

[Anonymous]

Thanks for this.

How would I amend the formula when using only the first two tabs of the demo to create the data model? Ideally I would like to add a Calculated Column to the factdata table to create an output similar the below but with the extra result column containing first month of the fiscal year.

Thanks again for your help.

[v-lili6-msft · Accepted solution]

hi, @Anonymous

You could try this formula to add a calculate column

new result = CALCULATE(MAX(Table1[Month Count]),FILTER(ALLEXCEPT(Table1,Table1[FiscalYear]),Table1[FiscalMonth]=1))

Best Regards,

Lin