Data as at same date in previous years

[greenskmachine]

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. 

[Shahid12523]

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
)
)

[v-sgandrathi]

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!

[v-sgandrathi]

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.

[v-sgandrathi]

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.

[MohamedFowzan1 · Accepted solution]

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
)

[FBergamaschi]

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

[greenskmachine]

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.