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 )
v-sgandrathi
Community Support
Community SupportHi 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-sgandrathiCommunity Support
Community SupportHi 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.