topic Re: Data as at same date in previous years in DAX Commands and Tips

Data as at same date in previous years

greenskmachine — Wed, 06 Aug 2025 22:27:25 GMT

Hi there,

I have a situation where I want to report data for each fiscal year (or other calendar dimension), up to the latest date where data is available, and using that date (day and month) for reporting data in previous years.

For example, my data is up 2nd August. Reporting by fiscal year I'd want this to report:

FY2025 - 1 July 2025 to 2 August 2025
FY2024 - 1 July 2024 to 2 August 2024
FY2023 - 1 July 2024 to 2 August 2023
etc

I have the below measure that does give me the data in the format I need, by finding the max data date, and using that to find the fiscal day of year to filter.

Measure =

var maxDate = CALCULATE(MAX(Weight[ProcessedDate]),ALL())

var dayPeriod = CALCULATE(MAX(DIM_DATE[Fiscal Day of Year]), DIM_DATE[Date] = maxDate)

RETURN

CALCULATE(SUM(Weight[Weight]),
DIM_DATE[Fiscal Day of Year] <= dayPeriod)

However I'm concerned doing this will skip over leap year days. What would you suggest? Thanks.

Re: Data as at same date in previous years

FBergamaschi — Thu, 07 Aug 2025 08:15:14 GMT

Hi,

do not worry of leap years, time intelligence will handle that smoothly.

To go to last year

CALCULATE ( [measure], DATEADD ( Calendar[Date], -1, YEAR )

If this helped, please consider giving kudos and mark as a solution

me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

Re: Data as at same date in previous years

greenskmachine — Fri, 08 Aug 2025 04:58:44 GMT

Thanks for the reply, but all this does it shift the full year's data one year.

It doesn't report the data as at the equvilant date in prior years.

Re: Data as at same date in previous years

MohamedFowzan1 — Fri, 08 Aug 2025 05:09:46 GMT

Hi greenskmachine

Could you try this

Measure = VAR maxDate = CALCULATE(MAX(Weight[ProcessedDate]), ALL()) VAR dayPeriod = CALCULATE(MAX(DIM_DATE[Fiscal Day of Year]), DIM_DATE[Date] = maxDate) RETURN CALCULATE(SUM(Weight[Weight]), DIM_DATE[Fiscal Day of Year] <= dayPeriod)

For YTD:

FiscalYearToDate = VAR maxDate = CALCULATE(MAX(DIM_DATE[Date]), ALL(Weight)) VAR currFiscalYear = CALCULATE(MAX(DIM_DATE[FiscalYear]), DIM_DATE[Date] = maxDate) VAR dayPeriod = CALCULATE(MAX(DIM_DATE[FiscalDayOfYear]), DIM_DATE[Date] = maxDate) RETURN CALCULATE( SUM(Weight[Weight]), DIM_DATE[FiscalYear] = currFiscalYear, DIM_DATE[FiscalDayOfYear] <= dayPeriod ) FiscalYearToDatePrevYear = VAR maxDate = CALCULATE(MAX(DIM_DATE[Date]), ALL(Weight)) VAR dayPeriod = CALCULATE(MAX(DIM_DATE[FiscalDayOfYear]), DIM_DATE[Date] = maxDate) VAR prevFiscalYear = CALCULATE(MAX(DIM_DATE[FiscalYear]), DIM_DATE[Date] = maxDate) - 1 RETURN CALCULATE( SUM(Weight[Weight]), DIM_DATE[FiscalYear] = prevFiscalYear, DIM_DATE[FiscalDayOfYear] <= dayPeriod )

Re: Data as at same date in previous years

v-sgandrathi — Sat, 09 Aug 2025 17:22:24 GMT

Hi greenskmachine,

Has your issue been resolved?
If the response provided by MohamedFowzan1 and FBergamaschi addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

Thank you for your understanding!

Re: Data as at same date in previous years

v-sgandrathi — Tue, 12 Aug 2025 11:39:05 GMT

Hi greenskmachine,

Just checking in -- have you had a chance to review and try the provided solution? Kindly share the status whenever you get a chance.

Looking forward to your response.

Re: Data as at same date in previous years

v-sgandrathi — Sat, 16 Aug 2025 06:25:43 GMT

Hi greenskmachine,

As we have not received a response from you yet, I would like to confirm whether you have successfully resolved the issue or if you require further assistance.

Thank you.

Re: Data as at same date in previous years

Shahid12523 — Fri, 22 Aug 2025 04:38:29 GMT

Use Month+Day instead of “Fiscal Day of Year” to avoid leap year issues.

Example:

Measure YTD Safe =
VAR maxDate = CALCULATE(MAX(Weight[ProcessedDate]), ALL(Weight))
VAR maxMD = VALUE(FORMAT(maxDate, "MMdd"))
RETURN
CALCULATE(
SUM(Weight[Weight]),
FILTER(
ALL(DIM_DATE),
VALUE(FORMAT(DIM_DATE[Date], "MMdd")) <= maxMD
&& DIM_DATE[Date] >= DATE(YEAR(DIM_DATE[Date]),7,1) // fiscal year start
)
)