Forum Discussion
Helper IVGet previous YYYY-MM value based on existing YYYY-MM value
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:
Now I want to create a previous year value measure so I did the following:
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
8 Replies
- amitchandak
Super User
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
- blynchdata
Resolver I
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.
009coInteresting. 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
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.
- 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.
