Fiscal Year to Date

[Adman]

Hi Guys  -

I'm new to Power Bi - have tried to investigate a solve but now spiing wheels!

I'm trying ot calculate the % Variance for a data item called an NWAU.

We have a fiscal year ending 30 June.

Formulas I have at the moment

NEW COLUMN -  CalYear = FORMAT([episode_end_date],("yyyy"))   - Not Cal Year is in text format ( is this the issues?)

and "episode_end_date" is in the raw data and is in data format

NWAU Final - is from the raw data

Measure: NWAU YTD = TOTALYTD ( SUM ('HIE Data'[nwau_final]), 'datekey'[Date], "06/30" )

Measure: NWAU PY_YTD = CALCULATE ('HIE Data'[NWAU YTD], DATEADD ('datekey'[Date], - 1, YEAR  ) )

New Table:  Calander = CALENDAR(MIN('HIE Data'[episode_end_date]),MAX('HIE Data'[episode_end_date]))

I was expecting that for 2017 the NWAU PY_YTD  (PY = Prior Year) the amount would be 40,864 instead of 13,832 - hence for some reason its not recognising the prior year.

Once NWAU PY_YTD  is calculating correctly the intended approach is

NWAY YTD % VAR = (NWAU YTD - NWAU PY_YTD)/ NWAU PY_YTD

And then to apply the Fiscal Year which has been determined as follows

NEW COLUMN -  CalYear = FORMAT([episode_end_date],("yyyy"))

NEW COLUMN: CalMonth = FORMAT([episode_end_date],"mm")

NEW COLUMN: FinYear-CurrentYY = CONCATENATE("FY",IF(([CalMonth])<"07",VALUE(FORMAT([CalYear],"YY")),VALUE(FORMAT([CalYear],"YY"))+1))

NEW COLUMN:  FinYear = IF(([CalMonth])<"07",VALUE(FORMAT([CalYear],"YYYY")),VALUE(FORMAT([CalYear],"YYYY"))+1)

thanks Adam

[v-sihou-msft]

@Adman

It's strange that your nwau_final shows same reuslt as YTD. YTD is already a cumulative calculation, when evaluated on Year level, it should return the result on the last day of Year.

I don't find big issue on both measures, just add ALL() in your YTD measure. It should return correct result.

Regards,

[Adman]

Hi Simon

Many thanks for your reply

Have updated formulas to reflect your ones so  - changes made in red

NWAU PY_YTD = CALCULATE ('HIE Data'[NWAU YTD], DATEADD ('datekey'[Date], - 1, YEAR  ) )

NWAU YTD = TOTALYTD ( SUM ('HIE Data'[nwau_final]),'datekey'[Date],ALL('datekey'[Date]), "06/30" )

there is no change to the prior year numbers..

thanks Adam

the "NWAU_final" may = YTD as it uses  calmonth to and calyear to derive the fiscial year  - it doesnt refer to the caldaner table!