Forum Discussion

009co's avatar
009co
Icon for Helper IV rankHelper IV
5 years ago
Solved

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...
  • amitchandak's avatar
    amitchandak
    5 years ago

    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's avatar
blynchdata
Icon for Resolver I rankResolver I
5 years ago

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's avatar
009co
Icon for Helper IV rankHelper IV
5 years ago

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's avatar
    blynchdata
    Icon for Resolver I rankResolver I
    5 years ago

    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!