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
)
v-sgandrathi's avatar
v-sgandrathi
Icon for Community Support rankCommunity Support
1 year ago

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's avatar
v-sgandrathi
Icon for Community Support rankCommunity Support
1 year ago

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's avatar
    v-sgandrathi
    Icon for Community Support rankCommunity Support
    1 year ago

    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.