Forum Discussion
Previous Month Value
The time intelligent functions rely on contiguous full year date ranges and you can see odd results if your dates don't cover full years.
The first suggestion is to add a calendar table. You can generate one in the model and it will look at the other date fields in the model to get the range.
Dates = VAR DateRange = CALENDARAUTO() RETURN ADDCOLUMNS( DateRange, "Year", YEAR ( [Date] ), "Month Name", FORMAT ( [Date], "mmmm"), "MonthNum", MONTH ( [Date] ), "Month Year", FORMAT ( [Date], "mmm-yyyy"), "YearMonthNum", YEAR ( [Date] ) * 100 + MONTH ( [Date] ), "QTR Year", "Q" & FORMAT ( [date],"Q-yyyy" ), "YearQtrNum", YEAR ( [Date] ) *100 + VALUE ( FORMAT ( [Date], "Q" )) )Or would you be able to share your .pbix file for us to review? It's very hard to answer the question "What's wrong with my data?" when we can't see it.
The time intelligent functions rely on contiguous full year date ranges and you can see odd results if your dates don't cover full years.
The first suggestion is to add a calendar table. You can generate one in the model and it will look at the other date fields in the model to get the range.
Dates =
VAR DateRange = CALENDARAUTO()
RETURN
ADDCOLUMNS(
DateRange,
"Year", YEAR ( [Date] ),
"Month Name", FORMAT ( [Date], "mmmm"),
"MonthNum", MONTH ( [Date] ),
"Month Year", FORMAT ( [Date], "mmm-yyyy"),
"YearMonthNum", YEAR ( [Date] ) * 100 + MONTH ( [Date] ),
"QTR Year", "Q" & FORMAT ( [date],"Q-yyyy" ),
"YearQtrNum", YEAR ( [Date] ) *100 + VALUE ( FORMAT ( [Date], "Q" ))
)Or would you be able to share your .pbix file for us to review? It's very hard to answer the question "What's wrong with my data?" when we can't see it.
Thank you for the quick response. A quick peek at the list tells me that the full year issue may indeed be the case. I will throw the Calendar table in just as a good exercise to verify it. Thank you for including that with your response!
I will repost once I have confirmed/fixed it.
Trent