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

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

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'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!