Issues with DAX Formula using SAMEPERIODLASTYEAR

Anything related to PowerPivot and DAX Formuale
halfdomefan
Posts: 1
Joined: Tue Jan 08, 2019 10:10 am

Issues with DAX Formula using SAMEPERIODLASTYEAR

Postby halfdomefan » Wed Jan 09, 2019 3:19 am

I'm a fairly new user of Dax and Power Pivot, so I have a question about a formula using SAMEPERIODLASTYEAR. I created a TOTALYTD formula, which worked fine. To get the Previous Year I referred to that formula using SAMEPERIODLASTYEAR, which did not work. The current data ended on 11.30.18, but the formula for the previous period kept picking up data through 12.31.17, when it should have ended on 11.30.17. After trying different versions of the formula and still coming up with the same result, I finally fixed the problem by changing the end date on my calendar to 11.30.18. (It was originally set for 12.31.18). A co-worker suggested that the formula should work even if I have a static calendar through 2025. Is this correct, or must I end my calendar earlier to get these formulas to function properly. Here are all the formulas involved:

Ad Count:=COUNTA('Table_MarketTrack_Combined'[Ad Date])

YTD Ad Count:=IF([Ad Count] = 0, BLANK(),
TOTALYTD([Ad Count],'Calendar'[Date]))

YTD Ad Count PY:=CALCULATE(
[YTD Ad Count],
SAMEPERIODLASTYEAR('Calendar'[Date]))

Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 1 guest