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

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.