topic Re: Calculation group for Previous FY duplicating values when the Month is in the X Axis - Not sure why? in DAX Commands and Tips

Calculation group for Previous FY duplicating values when the Month is in the X Axis - Not sure why?

Dallas7890 — Sat, 03 Aug 2024 13:41:18 GMT

I created a calculation group in my Power BI model to analyze overtime hours. This calculation group includes measures for:

Current Hours
Same Period Last Year (SPLY)
Previous Fiscal Year (FY)

Additionally, I implemented a Field parameter to dynamically change the Axis of my visuals. This parameter allows for the selection of various time periods, such as:

Fiscal Year (FY)
Month
Date
Start of Week
Day

The Problem is the setup works well in a Clustered column chart for most time periods, but I'm encountering an issue when selecting "Month" as the Axis. Specifically, the measure for the Previous FY displays the same value for each month, instead of showing distinct values for each month.

In the Clustered Column chart
In the X-axis - I put the Select Axis - which is just another Field parameter that is for the OT Type Parameter = Total Overtime Hours, Total Overtime Hours Weekday, Total Overtime Hours Weekend, etc...
In the Y-axis I put the OT Type Parameter - which again is just another Field Parameter that allows me to select by Director, City, Supervisor, Cost center, Affiliation, Employee.
In the Legend I put the Time Calculation that allows me to select between Current, SPLY, Previous FY.

Here is the measure I'm using in the Calculation group for the Previous FY:
Previous FY = CALCULATE(SELECTEDMEASURE(), PREVIOUSYEAR('Calendar'[Date]))

For reference, our fiscal year runs from April 1, 2023, to March 31, 2024.

I can't share the data due to confidentiality but I tried to give as much details as possible.

Re: Calculation group for Previous FY duplicating values when the Month is in the X Axis - Not sure why?

OwenAuger — Sat, 03 Aug 2024 23:03:36 GMT

Hi Dallas7890

PREVIOUSYEAR always returns a complete year preceding the year of the minimum date filtered. "Year" means calendar year unless the 2nd argument is provided, which should possibly be "03/31" in your case.

So for each month within in the same year, the Previous FY calculation item would be expected to return the same result.

Could you mock up a table showing what you would expect the Previous FY calculation item to return when you have chosen the Month field parameter value?

Also, what difference do you expect between Same Period Last Year and Previous Fiscal Year?

Re: Calculation group for Previous FY duplicating values when the Month is in the X Axis - Not sure why?

Dallas7890 — Sun, 04 Aug 2024 14:28:56 GMT

Date	Overtime Hours
4/15/2023	5
5/10/2023	8
6/20/2023	6
7/5/2023	7
8/18/2023	4
9/12/2023	10
10/3/2023	12
11/25/2023	15
12/13/2023	9
1/8/2024	14
2/17/2024	11
3/29/2024	3




Month	Previous FY OT Hrs
April	5
May	8
June	6
July	7
August	4
September	10
October	12
November	15
December	9
January	14
February	11
March	3

Hi Owen, Thanks for replying. Currently when Selecting the following Calculations for Current and Previous FY and then on Month as the Axis it just repeats the same FY amount for each month. It works okay when I select the other Field Parameter Axis below for Shift Date, Start of Week and Day but it repeats when Month?Mois is selected. I tried to also hard code the Fiscal year start and end date but it still does the same thing. I would like it to show the total amount for each month for the last FY not the entire FY amount.

I hope this helps explain my dilemma a bit better and I really appreciate your help with this

Thank you kindly,

This shows the amount with SPLY vs Previous FY

Even if I choose the Fiscal Year 2023-24 from the slicer up top. It still repeats the Previous FY repeats

Re: Calculation group for Previous FY duplicating values when the Month is in the X Axis - Not sure why?

OwenAuger — Tue, 06 Aug 2024 12:37:27 GMT

Thanks for the extra detail 🙂

A few questions:

If you instead choose SPLY instead of Previous FY, do you see the expected monthly values for the previous year?
(I'm assuming SPLY uses SAMEPERIODLASTYEAR or equivalent.)
Could you show me the code for these:
- The field parameter table (assuming it's created with DAX)
- All of the calculation items in the Time Parameter calculation group
Is your 'Calendar' table marked as a date table?

General comments

My main observation would still be that PREVIOUSYEAR is expected to shift the date filter to the complete previous year.
So the Previous FY results are actually what I would expect when you select Month on the field parameter.
However the results are not what I would expect when you select Shift Date, Start of Week and Day (though they are the results you want to see in the report).

If you could post a copy of a PBIX with sanitised data (OneDrive or other cloud storage link), I think that would be a great help in solving this 🙂

Regards

Re: Calculation group for Previous FY duplicating values when the Month is in the X Axis - Not sure why?

Dallas7890 — Tue, 06 Aug 2024 16:32:17 GMT

Hi Owen,

I actually managed to solve the FY month issue with this measure. It seems to work okay, was it the best way, not sure. Thanks again for replying and have a great day.

Previous FY =

VAR CurrentDate = MAX('Calendar'[Date])

VAR CurrentYear = YEAR(CurrentDate)

VAR CurrentMonth = MONTH(CurrentDate)

VAR PreviousFiscalYearStartYear = IF(CurrentMonth >= 4, CurrentYear - 1, CurrentYear - 2)

VAR PreviousFiscalYearMonth = IF(CurrentMonth >= 4, CurrentMonth, CurrentMonth + 12)

VAR PreviousFiscalMonthStart = DATE(PreviousFiscalYearStartYear, PreviousFiscalYearMonth, 1)

VAR PreviousFiscalMonthEnd = EOMONTH(PreviousFiscalMonthStart, 0)

VAR PreviousFiscalYearStartDate = DATE(PreviousFiscalYearStartYear, 4, 1)

VAR PreviousFiscalYearEndDate = DATE(PreviousFiscalYearStartYear + 1, 3, 31)

RETURN

IF(

HASONEVALUE('Calendar'[Month]),

CALCULATE(

SELECTEDMEASURE(),

DATESBETWEEN('Calendar'[Date], PreviousFiscalMonthStart, PreviousFiscalMonthEnd)

CALCULATE(

SELECTEDMEASURE(),

DATESBETWEEN('Calendar'[Date], PreviousFiscalYearStartDate, PreviousFiscalYearEndDate)

)