Forum Discussion

rrhascall's avatar
rrhascall
Helper II
4 years ago

Power BI Datasets

I'm creating a current and prior year trend chart using measures from another Power BI Dataset as a source.  The Prior Year measure used from the other dataset is not showing the actual prior year event counts.  Instead, it is also showing the current event counts.

The PY calculation from the other dataset is as follows:  

PYEvents = CALCULATE(DOWNTIME_EVENTS_BY_DEPARTMENT[TotalEvents], DATEADD(DateDim[Date],-1,YEAR ))
In my data model, i have a relationship between DOWNTIME_EVENTS_BY_DEPARTMENT and the Date table (DateDim).
 
Anyone know why the PY measure from the other dataset is not displaying the prior year counts?
 
fyi - i've tried to re-create a separate PY measure but I'm getting 'DATEADD' is expecting a contiguous selection error
 
Thanks!
Need Help

4 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    4 years ago

    Hi,

    Ensure tha the DOWNTIME_EVENTS_BY_DEPARTMENT column:

    1. Has proper dates (and not dates stored as text entries)
    2. Does not have time stamps
  • bcdobbs's avatar
    bcdobbs
    Community Champion
    4 years ago

    Based on the error I'd start by checking your date table. Date time functions require full years. Eg even if you only step over onto 2nd of january your date table would need all dates in that year.

    It's also possible that your date table is missing some dates.

  • rrhascall's avatar
    rrhascall
    Helper II
    4 years ago

    Thanks for your reply. 

    Date table should be ok.  It is generated based on Calendar function.  Has lots of future dates and contains more historical dates than my event data:

    DateDim =
    VAR BaseCalendar =
    CALENDAR( Date(2017, 1, 1), Date(2040,12,31))
     
    There are gaps in the Event data but trending works fine in the source .pbix workbook/dataset.  My real dilema is why the PY event counts in the linked dataset are not correct when used
    in the destination workbook.
    • bcdobbs's avatar
      bcdobbs
      Community Champion
      4 years ago

      I'd follow Ashish_Mathur suggestion and make sure date columns in your new model are all set as Date and not Datetime. If that doesn't work come back and we'll see if we can dig deeper 🙂