CODEWITHSUNDEEP
powerbidax
jordanar189
jordanar189

Reputation: 1

Power BI Measure pulling in wrong date range with DATESBETWEEN function

I have a measure that pulls in revenue as follows:

Revenue = SUM(Orders[Sales]) + SUM(Shipping[Charge])

I then have another measure that pulls in last year's revenue in the same date range as follows:

LY Revenue = CALCULATE([Revenue],ALL('Date Table'[Date]),DATESBETWEEN('Date Table'[Date],MIN('Date Table'[LY Date])+1,MAX('Date Table'[LY Date])+1))

My issue is with the LY Revenue measure. It is working 99% of the time, except when filtered to the first period of 2023. When looking at that period, rather than pulling revenue from P1 of 2022, it brings in the revenue from the entire year of 2022:

enter image description here

The weird thing is that this doesn't happen in any other period, even in P1 of 2024. Our revenue data only goes back through 2022, so that year expectedly has no values for LY Revenue. The LY Date column from my date table also has no values in 2022, since there are no 2021 dates in that table.

What's going on here and how can I fix it?

I'm trying to create a measure that pulls in last year's revenue while filtered to a certain timeframe. I expected that each period would show the revenue from the same period in the previous year, and while this is what's happening for most periods, P1 of 2023 is showing the entirety of last years revenue.

Upvotes: 0

Views: 47

Answers (1)

Amira Bedhiafi
Amira Bedhiafi

Reputation: 1

I think you need to change the LY definition :

LY Revenue = CALCULATE([Revenue], SAMEPERIODLASTYEAR('Date Table'[Date]))

Upvotes: 0

Related Questions