Could Someone Double Check These Measures

[IamTDR]

Morning
Trying to go a bit out of my comfort zone by adding a slicer to switch between MTD & YTD.
So I wrote the two measures below and the values work as expected.  First I was wondering if these could be written any better?  Second, more importantly, do these look like they will dynamically work upon the start of our company's new fiscal year in May.

Thanks in advance for reviewing.

CY Net Sales = SWITCH([Selected Period],

"Period", CALCULATE(SUM(Source_TerritorySales[sale_net]),FILTER(Dim_Date,Dim_Date[Fiscal Year]=2024)),

"YTD", CALCULATE(SUM(Source_TerritorySales[sale_net]),DATESYTD(DATEADD(Dim_Date[Date],0,YEAR),"4/30")),

BLANK() )

PY Net Sales = SWITCH([Selected Period],

"Period", CALCULATE(SUM(Source_TerritorySales[sale_net]),FILTER(Dim_Date,Dim_Date[Fiscal Year]=2023)),

"YTD", CALCULATE(SUM(Source_TerritorySales[sale_net]),DATESYTD(DATEADD(Dim_Date[Date],-1,YEAR),"4/30")),

BLANK() )

[audreygerred]

Hi! I would suggest creating a custome date date that handles the fiscal year. I have a blog post about this which has a link to the reference table I love using: http://powerbiwithme.com/2023/08/01/the-custom-date-table-edition/

I would avoid explicitly referring to a certain date in your measure because you will be forced to maintain these each year, and then if someone wante dto compare 2022 to 2021 instead of 2024 to 2023, the measures won't work.

Here is the DAX syntax for YTD: TOTALYTD function (DAX) - DAX | Microsoft Learn and for MTD: TOTALMTD function (DAX) - DAX | Microsoft Learn

Whenever using the FILTER function, avoid logic like FILTER( factTable, someColumn = logic) use FILTER( factTable[someColumn], factTable[someColumn] = logic) instead: https://www.sqlbi.com/articles/filter-arguments-in-calculate/

[IamTDR]

Thanks for the reply.  Yes, I do have a dynamic Date table (Dim_Date) attached to my BI report based on the date fields from my Fact Table (Source_TerritorySales)

Within the report, I have requests to show trends of past five year sales but I also have requests to show Currrent Year vs Prior Year vs Prior Prior Year.  Those Current Year and Prior Year measures I have used explicite dates.  I would like to avoid so as well but haven't figured it out just yet