cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Post Partisan

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

4 REPLIES 4
Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Post Partisan

@Greg_Deckler It looks correct, but I´m getting 100% on all rows.

Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Post Partisan

Unfortunately, same result.

My cases measure is:

Cases = CALCULATE(COUNTROWS(Cases) ,Cases[statuscodename] <> "Cancelled")