Forum Discussion

BugmanJ's avatar
BugmanJ
Helper V
4 years ago

Not a valid function name?

Hello All,

Powerbi giving me the error "Select Slicer" is not a valid table, variable or function name.

Formula:

Slicer Selection = Var Select_Slicer = max(Datetable[Date].[Date])
Return
(CALCULATE (SUM([Total Sales]),'TableSales'[Day Date] > Select_Slicer()-8)) -
(CALCULATE (SUM([Total Sales]),'TableSales'[Day Date] = Select_Slicer()))/7
 
Idea:
Being that the above calculation calculates the last average of the the last 7 days of sales depending on the slicer date chosen
Need Help

2 Replies

  • nvprasad's avatar
    nvprasad
    Solution Sage
    4 years ago

    Hi BugmanJ,

     

    Can you try below Dax

     

    Slicer Selection = Var Select_Slicer = max(Datetable[Date].[Date])
    Return
    (CALCULATE (SUM([Total Sales]),'TableSales'[Day Date] > Select_Slicer-8)) -
    (CALCULATE (SUM([Total Sales]),'TableSales'[Day Date] = Select_Slicer))/7

     

    Appreciate a Kudos! ‌‌
    If this helps and resolves the issue, please mark it as a Solution! ‌‌

    Regards,
    N V Durga Prasad

     

    • BugmanJ's avatar
      BugmanJ
      Helper V
      4 years ago

      Hi nvprasad ,

      This works but it brings a new problem. I have to use the original sales date rather then the Datetable I have linked to it.

      So, the equation given gives the last 7 days of the slider, great! But if the store has closed and hasnt supplied data for say 5 months, its still calculating the last 7 days of that store. Any thoughts how I can get it to ignore and any inkling to whats wrong with using a datetable that works for everything else?

       

      I tried to fix it, recreating the measure and used the Date table (Datetable[Date]) not Datetable[Date].[Date] (What is the . part for?) This creates the blanks, but now gives me the wrong numbers when moving the slider

      Infact moving the slider i can see its ignoring the 7 days....

      So in short:
      1) Works, but includes data from stores that closed months ago (this number doesnt move until that date is reached). Also have to use table date not datetable

      2) If i try using datetable, it then ignores 7 days

      Thanks