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 reporting data in previous years. 

For example, my data is up 2nd August. Reporting by fiscal year I'd want this to report: 

FY2025 - 1 July 2025 to 2 August 2025
FY2024 - 1 July 2024 to 2 August 2024
FY2023 - 1 July 2024 to 2 August 2023
etc

I have the below measure that does give me the data in the format I need, by finding the max data date, and using that to find the fiscal day of year to filter. 

 

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)

However I'm concerned doing this will skip over leap year days. What would you suggest? Thanks. 
  • 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
)

7 Replies

    • greenskmachine's avatar
      greenskmachine
      Frequent Visitor
      1 year ago

      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. 

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

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