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
)
MohamedFowzan1's avatar
MohamedFowzan1
Icon for Super User rankSuper User
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
)