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

[rohanjasp]

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

[Anonymous · Accepted solution]

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.

[lbendlin]

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.