topic Re: Prior Year Same Month - When dates do not fall into same period in DAX Commands and Tips

Prior Year Same Month - When dates do not fall into same period

rohanjasp — Sun, 29 Sep 2024 20:51:14 GMT

Hi All,

anyone have ideas on how to solve the below.

Have a Dates table where each date has a fiscal month and fiscal year. as per the screenshot below, date is joined to Fact_ table which has sales transactions.

Need to calculate sales for same fiscal period but prior year sales number, however dates for Fiscal period 1 2025 has dates from 7/1/2024 to 8/3/2024 and Fiscal Period 1 2024 has dates 7/1/2023 to 8/5/2023, anyone know how i can calculate prior year f1 2024 sales, cannot use time intelligence dax functions as the dates falling into FY 1 2025 and FY 1 2024 are not the same .

Thanks

Re: Prior Year Same Month - When dates do not fall into same period

lbendlin — Sun, 29 Sep 2024 22:18:43 GMT

What is our expected outcome? Only consider FY24 Period 1 dates until 8/3/2023 ?

Note that 7/1/2023 and 7/1/2024 are not equivalent - they fall on different weekdays. So your comparison will be skewed from the very beginning.

Re: Prior Year Same Month - When dates do not fall into same period

Anonymous — Tue, 01 Oct 2024 04:11:40 GMT

Hi rohanjasp ,

According to your screenshot, I think there is a date table with [Fiscal Year] and [FY Startno] to determine the year and month of the fiscal.

I think you can use them in your calculation. You can try code as below to create a measure.

Previous Year = CALCULATE ( SUM ( 'TableName'[ColumnName] ), FILTER ( ALLSELECTED ( 'DateTable' ), 'DateTable'[Fiscal Year] = MAX ( 'DateTable'[Fiscal Year] ) - 1 && 'DateTable'[FY Startno] = EOMONTH ( MAX ( 'DateTable'[FY Startno] ), -13 ) + 1 ) )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.