Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Previous Year Total With Non-Continuous Dates

Hello,

I have a barchart that plots data over a period of time. Users are able to select and multiselect individual bars to filter the dashboard to just the months that are selected from the bar chart. 

 

I would like to display the prior year total for the same period of the previous year. The following calculation is working great when single or multiple Continuous months are selected:

 

LY = CALCULATE(SUM(SALES),FILTER(SALES_TABLE, SALES_TABLE[DATE] IN (DATEADD(VALUES(CALENDARTABLE[DATE],-1,YEAR)))

 

When a user selects non-Continuous months, like March, June, September. The calculation raises an error that DATEADD requires continious dates. I've tried wrapping the calculation in IFERROR and just returning something else to move past this, but that does not work either. 

 

I have the following questions:

1.) Is there a calculation I can use to accurately aggregate the previous years result of multiple non-continious dates?

2.) If #1 is not possible, is it possible to somehow suppress this error and return a static value when this is encountered? Worst case I can add a tooltip that calls out the limitation of the calculation and display a placeholder value instead.     

9 Replies

  • davehus's avatar
    davehus
    Icon for Memorable Member rankMemorable Member

    Hi Anonymous 

     

    Try 

    CALCULATE([Sales],SAMEPERIODLASTYEAR('Date Table'[DateKey]))
    • Anonymous's avatar
      Anonymous
      Not applicable

      "SAMEPERIODLASTYEAR expects a contigious selection when the date column comes from a table on the 1-side of a bi-directional relationship."

       

       I've tried swapping the relationships from Calendar-> Sales, single.

      Sales -> Calendar, single. 

      Sales <-> Calendar, both.

      Same error each time.

      • davehus's avatar
        davehus
        Icon for Memorable Member rankMemorable Member

        Are you passing the date from the fact table or the date from your date table into the measure. It should be the date table.

        If the date table are the dates continuos as in 1st January to 31st December with no missing date values?

        Contgious means in sequence.