cancel
Showing results for
Did you mean:
Frequent Visitor

## Calculating Prior Sales at Monthly Level - Leap Year

Hi all - Having some trouble figuring out a calculated measure. My report allows users to select time ranges down to the monthly level. The below measure calculates last year's sales, accounting for incomplete months (i.e. sales of 1/1/2023 - 1/26/2023 will be compared to 1/1/2022 - 1/26/2022, even though the user has selected all of January 2023).

``````Sales PY =
IF(
[Sales] <> BLANK(),
IF(
HASONEVALUE('Calendar'[Date]),
CALCULATE(
[Sales],
SAMEPERIODLASTYEAR('Calendar'[Date])
),
IF(
HASONEVALUE('Calendar'[Year]),
CALCULATE(
[Sales],
DATESBETWEEN(
'Calendar'[Date],
EDATE(MIN('Calendar'[Date]),-12),
EDATE(MAX('Calendar'[Date]),-12)
)
)
)
)
)``````

However, I'm running into a problem when charting this data out and accounting for a leap year. My dataset has data starting 1/1/20, so in the chart below the lack of prior year sales for 2020 makes sense. But in 2021, February is missing, which I believe is because there was a 2/29/2020 and there is no 29th of February in 2021.

Can anyone help me as to why my measure won't calculate prior year sales for February 2021? Many thanks!

1 ACCEPTED SOLUTION
Frequent Visitor

I have solved this issue. I rewrote my formula to account for partial months based off of today's date.

``````Sales PY =
IF(
MONTH(MAX('Calendar'[Date])) = MONTH(TODAY()),
CALCULATE(
[Sales],
DATESBETWEEN(
'Calendar'[Date],
EDATE(MIN('Calendar'[Date]),-12),
EDATE(MAX('Calendar'[Date]),-12)
)
),
IF(
HASONEVALUE('Calendar'[Year]),
CALCULATE(
[Sales],
SAMEPERIODLASTYEAR('Calendar'[Date])
)
)
)``````
Frequent Visitor

I have solved this issue. I rewrote my formula to account for partial months based off of today's date.

``````Sales PY =
IF(
MONTH(MAX('Calendar'[Date])) = MONTH(TODAY()),
CALCULATE(
[Sales],
DATESBETWEEN(
'Calendar'[Date],
EDATE(MIN('Calendar'[Date]),-12),
EDATE(MAX('Calendar'[Date]),-12)
)
),
IF(
HASONEVALUE('Calendar'[Year]),
CALCULATE(
[Sales],
SAMEPERIODLASTYEAR('Calendar'[Date])
)
)
)``````