Population at start of Financial Year

[TashanTheEarl]

Hello All,

I've been stuck on this one over a month now and accepted I need help.

I need a DAX Calculation that works out the population (Sum of Members) at the start of that financial year. This needs to work while being sliced by different dimensions. (Currently Membership Grade, Membership Group and Country)

This is my 1st time posting in the community and I've provided the additional details below I think will help but if any other details are needed to help assist please let me know.

Financial year runs from Nov - Oct

Fact table aggregated at Monthly level

The most accurate capture period for Start of financial year is by taking the month before the calculations are done EOM fo start of year

Membership Monthly Sample

Category

Country

Finance Region

FY

FY Year sort

Gender

ISO 2

Membership Grade

Membership Group

Members

MemberOrganisationType

MonthYear

PreviousFY

Region

Reporting Period End

Reporting Period Start

Status Sub Group

StudyMethod

UK Country

Joiners

Afghanistan

SINGAPORE

FY 15

15

AF

Affiliate

Affiliate

20

201503

FY 14

Other Regions

31/03/2015 00:00

01/03/2015 00:00

No Data

Joiners

Afghanistan

SINGAPORE

FY 15

15

AF

Certificate

Student & Part Qualified

406

201412

FY 14

Other Regions

31/12/2014 00:00

01/12/2014 00:00

No Data

Joiners

Afghanistan

SINGAPORE

FY 15

15

AF

Certificate

Student & Part Qualified

20

201503

FY 14

Other Regions

31/03/2015 00:00

01/03/2015 00:00

No Data

Date Table

Date

DateKey

Day

DayName

Exam Series

FY

FY Sort

FY_Period

IsWeekDay

Last Year

Month

Month SO

MonthName

MonthYear

Period

Quarter

ShortMonthName

Start of FY

Start of Last FY

Start of Month

Year

01/11/2014 00:00

20141101

1

Saturday

01/11/2014 00:00

FY 15

15

201501

TRUE

01/11/2013 00:00

11

1

November

Nov-14

201411

1

Nov

01/11/2014 00:00

01/11/2013 00:00

01/11/2014 00:00

2014

02/11/2014 00:00

20141102

2

Sunday

01/11/2014 00:00

FY 15

15

201501

TRUE

02/11/2013 00:00

11

1

November

Nov-14

201411

1

Nov

01/11/2014 00:00

01/11/2013 00:00

01/11/2014 00:00

2014

03/11/2014 00:00

20141103

3

Monday

01/11/2014 00:00

FY 15

15

201501

TRUE

03/11/2013 00:00

11

1

November

Nov-14

201411

1

Nov

01/11/2014 00:00

01/11/2013 00:00

01/11/2014 00:00

2014

04/11/2014 00:00

20141104

4

Tuesday

01/11/2014 00:00

FY 15

15

201501

TRUE

04/11/2013 00:00

11

1

November

Nov-14

201411

1

Nov

01/11/2014 00:00

01/11/2013 00:00

01/11/2014 00:00

2014

05/11/2014 00:00

20141105

5

Wednesday

01/11/2014 00:00

FY 15

15

201501

TRUE

05/11/2013 00:00

11

1

November

Nov-14

201411

1

Nov

01/11/2014 00:00

01/11/2013 00:00

01/11/2014 00:00

2014

[amitchandak]

@TashanTheEarl , Firstnonblankvalue can help

example

calculate(Firstnonblankvalue (Table[Date], Sum(Table[Values]), filter(all(Date), Date[Year] = max(date[Year]) ) )

Power Bi DAX Functions openingbalancemonth, openingbalancequarter, openingbalanceyear, firstnonblankvalue, and parallelperiod.Opening Stock, First value of period: https://youtu.be/6lzYOXI5wfo

[TashanTheEarl]

Hello Amitchandak,

Thank you so much for your response I've not reached a solution yet but this has defintely been a step in the right direction compared to previous attempts. It set me on the path of time intelligence functions I didn't know exists but I couldn't get OPENINGYEARBALANCE working.

I've ended up with the below formula which is giving the correct output but looks strange when you put it along side another measure.

Any further guidance would be greatly appreciated

StartOfFy = CALCULATE([1.0 - Members]

                            ,STARTOFMONTH(

                                            PREVIOUSDAY(

                                                        STARTOFYEAR('Date Table'[Date]

                                                                ,"31-10"

))))