Get previous YYYY-MM value based on existing YYYY-MM value

[009co]

My dataset has rows by year and month. It  has a related calendar table. In my reporting I am using the calendar table YYYY-MM value.

The calendar table also has a proper date column for the last day of the month. I can create a previous year date using:

date PY = DATEADD('Calendar'[Date],-1,year)

Now I want to create a previous year value measure so I did the following:

Actual PY = CALCULATE([Actual],DATEADD('Calendar'[Date],-1,YEAR))

However, while this doesn't give an error, it doesn't return any value eg it is blank.

This probably has something to do with the format of the date value eg it can't find dataset rows by YYYY-MM because the previous year date is a proper date format.

How do I get this work?

[009co]

My original date table and fact table relationship was based on an integer representation of a date eg 20210101 however creating a new calculated column that was a proper date in both of these tables  and using that new proper date for the relationship made time intelligence work properly.

[blynchdata]

Sometimes when this happens to me, I bypass the DATEADD() function. 

I'll create a variable:

PY = MAX("Calendar Year Number in Date Dimension Table") - 1

Comment: So this takes the current Year Number in the context, and subtracts 1 manually.

Then in my measure:

Measure = CALCULATE([Actual], "Calendar Year Number in Date Dimension Table" = PY)

This can often work for me, but it does require you have a Year Number in your Date Dimension. 

[009co]

Interesting. Gave this a try but am getting following error when doing:

PY = MAX('Calendar'[Year]) - 1 <= this calculates previous year value, i formatted General with no decimals (couldn't choose Text)

Measure = CALCULATE([Actual], 'Calendar'[Year] = [PY]) <= this gives error: "A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

Did I do this as you explained? It was clear exactly what you meant by "Calendar Year Number in Date Dimension Table"?

Thanks

[blynchdata]

I'm not sure there should be square brackets around the PY variable in your measure as it may think it's a column or a measure. I've confirmed this way does work for me.

I'm looking at some other ways that I've done this and I found that it works well, using CalculateTable() as a kind of filter within the CALCULATE() of [Actual].

Measure = CALCULATE (
[Actual],
CALCULATETABLE ( DATEADD ( 'Date Dimension'[Date_Formatted_Column], -1, YEAR ) )
)

[009co]

RE first suggestion, was it to create a new column with a variable and result to be returned as follows? This didn't create any errors, but was just blank:

Measure =
VAR
    PY = FORMAT(DATEADD('Calendar'[Date],-1,YEAR), "YYYY")
RETURN
    CALCULATE([Actual], 'Calendar'[Year] = PY)

RE second suggestion, it just returns the current year actual value:

Measure = CALCULATE([Actual],CALCULATETABLE(DATEADD('Calendar'[Date],-1,YEAR)))

This is same behaviour of my original DATEADD formula, after I marked my date table as a date table.

Actual PY = CALCULATE([Actual],DATEADD('Calendar'[Date],-1,YEAR))

It is like minus 1 from year is same as current year.

[blynchdata]

Apologies, I didn't know you were trying to make a column, I thought it was a measure.

I will bow out here as I am unsure!

[009co]

Hey thanks for answer @amitchandak

Check -

calendar table is marked as date table - It wasn't, I didn't know this so thanks for teaching me something new. However, I am not able to mark my table as date table. I get message that claims there are gaps in dates ... I suspect it is because table doesn't have complete years even though it spans multiple years.

It has previous year dates in the date column. I doubt you need date PY  - it did have all required dates past and present

The Fact Date should not have any timestamp - the table comes from SQL server db, and is formatted as text. So this might be problem too.

[amitchandak · Accepted solution]

@009co , As long as YYYYMM is coming from the calendar table, that should not give issue.

Measure is correct

Actual PY = CALCULATE([Actual],DATEADD('Calendar'[Date],-1,YEAR))

Check -

calendar table is marked as date table

It has previous year dates in the date column. I doubt you need date PY 

So for this year date column should have date for 2020 too

The Fact Date should not have any timestamp

Why Time Intelligence Fails - Power bi 5 Savior Steps for TI: https://youtu.be/OBf0rjpp5Hw