cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## DAX YTD measure help

Hi,

I am trying to create a measure that allows me to see a count of vehicle crashes using year-to-date, for previuos years. I have data from 01/01/2017 - 04/30/2020. This is the measure I have so far, but I am  getting an error that reads "An argument of function "Date" has the wrong data type or the result is too large or too small."

Previous Year-to-Date =

VAR currdate =
CALCULATE(LASTDATE('10 Texas Crashes'[Date]), ALLSELECTED('00 Calendar'[Date]))
VAR startyear =
FIRSTDATE( '00 Calendar'[Date] )-365
VAR enddate =
DATE(YEAR(startyear),MONTH(currdate), DAY(currdate))

RETURN
Calculate(
DISTINCTCOUNT('10 Texas Crashes'[Crash ID]),
DATESBETWEEN ( '00 Calendar'[Date], startyear, enddate ))

I would like to add the measure as a line, on the chart below.

2 REPLIES 2
Super User

Couple ways to do this. If you have a date table, you could use your current year to date measure like this:

Previous Year = CALCULATE([Current YTD],SAMEPERIODLASTYEAR('00 Calendar'[Date]))

Or, in your code (sort of):

``````Previous Year-to-Date =

VAR currdate = MAX('00 Calendar'[Date])
VAR startyear = YEAR(currdate) - 1
VAR startdate = DATE(YEAR(startyear),1,1)
VAR enddate = DATE(YEAR(startyear),MONTH(currdate), DAY(currdate))
RETURN
Calculate(
DISTINCTCOUNT('10 Texas Crashes'[Crash ID]),
FILTER('00 Calendar',[Date] >= startdate && [Date] <= enddate ))``````

If none of that works, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

@Anonymous

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Thank you for your feedback! I tried the measure, but it only shows as one value. I would like to see the previuos year to date of the prior years on on the chart as well(2017-2019).