Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Previous Year Calculation -1 day

Hi!   So I'm trying to "move" alla previous year metrics one day.  For example; 2022-11-18 should be compared to 2021-11-19. I have solved it on a line by line basis but the sum of days doesn't wor...
Need Help
  • PaulDBrown's avatar
    PaulDBrown
    3 years ago

    Ok, try this then:

    PY =
SUMX (
    VALUES ( Table[Date] ),
    CALCULATE (
        [Sales],
        FILTER (
            ALL ( 'Date'[Date] ),
            'Date'[Date]
                = MAX ( OrderData[OrderDate] ) - 364
        )
    )
)
PaulDBrown's avatar
PaulDBrown
Icon for Community Champion rankCommunity Champion
3 years ago

Try:

PY with total = 
SUMX(VALUES(Table[Date]), [PY Sales])
Anonymous's avatar
Anonymous
Not applicable
3 years ago

PaulDBrown 

 

Hi! The solution works, but its' not ideal because then I need to create 2 metrics for all PY calculations.

 

In other words, ideally I would just have 1 metric for PY rather than having to create first the "YTD"* PY calculation and then the sum calculation of that.

 

*YTD = Max date being last orderdate

  • PaulDBrown's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Ok, try this then:

    PY =
SUMX (
    VALUES ( Table[Date] ),
    CALCULATE (
        [Sales],
        FILTER (
            ALL ( 'Date'[Date] ),
            'Date'[Date]
                = MAX ( OrderData[OrderDate] ) - 364
        )
    )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      PaulDBrown Thank you!