Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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,
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
97 | |
82 | |
67 | |
61 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |