Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Creating A Prior Year Amount Column

I have a measure that calculates the prior year amount for Est Rev. I want to use the KPI Power Matrix and to do that I need to have the prior year Est. Rev. in a column. This proving to be more difficult that I had thought it would be. 

Here is my measure for PY: 

Total Est Rev PY = CALCULATE([Total Est Rev],sameperiodlastyear('Calendar'[Date]))

Here is what I am trying to do for the column and it is not working.

Total Est. Rev PY = CALCULATE([Total Est Rev],Filter(PREVIOUSYEAR('Calendar'[Year])=YEAR('Quotes Estimates US'[JobSDate]-1)&&'Quotes Estimates US'[Job Start Month]='Quotes Estimates US'[Job Start Month]))
I am getting the error that my filter has not enough arguments that were passed to it, that it needs at minium 2 functions and I thought I did have 2 functions.
Any help would be most appreciated.
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago

    Hi Anonymous ，

     

    I‘m not sure whether your measure [Total Est Rev PY] checked right?I'm guessing whether you're using a  calendar date to calculate the value?If so, you need to choose a table date instead of a calendar date,then you will see as below:

     

     

    As for the measure

     

    Total Est. Rev PY = CALCULATE([Total Est Rev],Filter('Quotes Estimates US'[Estimate Revenue],PREVIOUSYEAR('Calendar'[Year])=YEAR('Quotes Estimates US'[JobSDate]-1)&& 'Calendar'[Month]='Quotes Estimates US'[Job Start Month]))

     

     

    Should be corrected as below:

     

     

    Total Est. Rev PY = CALCULATE([Total Est Rev],Filter('Quotes Estimates US'[Estimate Revenue],PREVIOUSYEAR('Calendar'[Year])=YEAR('Quotes Estimates US'[JobSDate])-1)&& 'Calendar'[Month]='Quotes Estimates US'[Job Start Month]))

     

    You missed a ")" after the function "Year“，if there still has errors,check what I have said above, I guess the problem happens in the "date" you are choosing.

     

    Best Regards,
    Kelly

6 Replies

  • kentyler's avatar
    kentyler
    Solution Sage
    6 years ago

    Total Est. Rev PY =

       CALCULATE(
          [Total Est Rev],
          Filter(<table expression> missing, 
             PREVIOUSYEAR('Calendar'[Year])=YEAR('Quotes Estimates US'[JobSDate]-1)&&'Quotes Estimates US'[Job Start Month]='Quotes Estimates US'[Job Start Month]
                   )
          )
    you seem to have just one expression in your filter function, although it is a complex expression and uses && to combine 2 columns you want to filter

    and you are missing the <table> argument to filter

    https://dax.guide/filter/

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Ok so now it is saying that hte filter function exepcts a table expression for arguement '1', but a string or numeric expression was used. 

      Total Est. Rev PY = CALCULATE([Total Est Rev],Filter('Quotes Estimates US'[Estimate Revenue],PREVIOUSYEAR('Calendar'[Year])=YEAR('Quotes Estimates US'[JobSDate]-1)&& 'Calendar'[Month]='Quotes Estimates US'[Job Start Month]))
      • v-kelly-msft's avatar
        v-kelly-msft
        Community Support
        6 years ago

        Hi Anonymous ，

         

        I‘m not sure whether your measure [Total Est Rev PY] checked right?I'm guessing whether you're using a  calendar date to calculate the value?If so, you need to choose a table date instead of a calendar date,then you will see as below:

         

         

        As for the measure

         

        Total Est. Rev PY = CALCULATE([Total Est Rev],Filter('Quotes Estimates US'[Estimate Revenue],PREVIOUSYEAR('Calendar'[Year])=YEAR('Quotes Estimates US'[JobSDate]-1)&& 'Calendar'[Month]='Quotes Estimates US'[Job Start Month]))

         

         

        Should be corrected as below:

         

         

        Total Est. Rev PY = CALCULATE([Total Est Rev],Filter('Quotes Estimates US'[Estimate Revenue],PREVIOUSYEAR('Calendar'[Year])=YEAR('Quotes Estimates US'[JobSDate])-1)&& 'Calendar'[Month]='Quotes Estimates US'[Job Start Month]))

         

        You missed a ")" after the function "Year“，if there still has errors,check what I have said above, I guess the problem happens in the "date" you are choosing.

         

        Best Regards,
        Kelly

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Try

    Total Est Rev PY = CALCULATE([Total Est Rev],dateadd('Calendar'[Date],-1,year))

     

    Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
    Thanks. My Recent Blog -
    Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
    Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

    Connect on Linkedin

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      This still results in an empty column for my Total Est Rev PY.

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Do you have Job start date . then Try

     

    Total Est Rev PY = CALCULATE([Total Est Rev],dateadd('Calendar'[Date],-1,year))
    var _maxDate =maxx(dateadd('Quotes Estimates US','Quotes Estimates US'[JobSDate],-1,Year))
    var _min_date =minx(dateadd('Quotes Estimates US','Quotes Estimates US'[JobSDate],-1,Year))

    // OR this one if date is selected from calendar
    /*
    var _maxDate =maxx(dateadd('Calendar','Calendar'[Date],-1,Year))
    var _min_date =minx(dateadd('Calendar',Calendar'[Date],-1,Year))


    */
    return
    = CALCULATE([Total Est Rev],
    Filter('Quotes Estimates US','Quotes Estimates US'[JobSDate] >=_min_date =minx && 'Quotes Estimates US'[JobSDate]<=_maxDate)