Forum Discussion

greenskmachine's avatar
greenskmachine
Frequent Visitor
1 year ago
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...
  • MohamedFowzan1's avatar
    MohamedFowzan1
    1 year ago

    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
)
Shahid12523's avatar
Shahid12523
Icon for Community Champion rankCommunity Champion
1 year ago

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