Forum Discussion
Solved
Data as at same date in previous years
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...
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
Super User
Super UserHi,
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
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.
