Forum Discussion

setis's avatar
setis
Post Partisan
3 years ago

Using ALL for last year

Hi experts,

I'm calculating the weight of the number of cases on each day of the month like this:

 

I have 2 questions.
1.- How can I calculate on a measure on the same table the [Weight day on month] for same day last year (SAMEPERIODLASTYEAR gives me 100% on every row)
2.- How to calculate the [Weight day on month] for same day +1 last year (on Jan 1st 2022, the value for Jan 2nd 2021)

 

Thanks a lot in advance

Need Help

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    setis Maybe:

    Measure =
  VAR __Date = MAX('Date'[Date])
  VAR __LYDate = DATE(YEAR(__Date)-1,MONTH(__Date),DAY(__Date))
RETURN
  CALCULATE([Weight day on month],'Date'[Date] = __LYDate)

Measure 2 =
  VAR __Date = MAX('Date'[Date])
  VAR __LYDate = DATE(YEAR(__Date)-1,MONTH(__Date),DAY(__Date)+1)
RETURN
  CALCULATE([Weight day on month],'Date'[Date] = __LYDate)
      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        3 years ago

        setis The scintillating joy of CALCULATE. What is your Cases measure? The formula? Could maybe try this:

        Measure =
  VAR __Date = MAX('Date'[Date])
  VAR __LYDate = DATE(YEAR(__Date)-1,MONTH(__Date),DAY(__Date))
RETURN
  CALCULATE([Weight day on month],ALL('Date'), 'Date'[Date] = __LYDate)