Previous Month Value

[TrentS]

Hi,

Tearing my hair out on this one due to inconsistency, so I need help.

I have two data tables with similar structure. (No date table...yes, I know...). These tables are NOT related.

Each table contains a Date field formatted as Date Type. The format for both is 'Month Year'

I created a simple measure with PREVIOUSMONTH in one and it works as expected. The second however returns blanks using the same formula (adjusted for field names).

Same thing applies to a column instead of a measure. All items are hosted by the parent data table.

I have walked through each one making sure all of the formats and types match. I have read through multiples articles from @Greg_Deckler on the subject, as well. I am relatively certain it has to do with how the second table Date field is structured or formatted but cannot find any inconsistency with the table where the measure does work.

Looking for suggestions on where to go next. Unsure why it would work on one and not the other. I just need the value returned from the previous time period (month). Why would the Time Intelligence function not work on the second table.? (Again, there is no Date Table relational issue here.)

Thanks for any insight,

Trent

[jdbuchanan71 · Accepted solution]

@TrentS 

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.

[TrentS]

Once I got the DBA to expand the date range in the table, it operated as expected. Thanks for the insight!

[TrentS]

@jdbuchanan71 

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