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

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