Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
009co
Helper IV
Helper IV

Get 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:

 
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?
 

 

1 ACCEPTED SOLUTION
amitchandak
Super User
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

View solution in original post

8 REPLIES 8
009co
Helper IV
Helper IV

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
Resolver I
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. 

 

 

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

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 ) )
)

 

 

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.
 
 

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
Helper IV
Helper IV

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
Super User
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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.