Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Comparing current vs last year sales - multiple date columns

Hello,   I have a sales table that looks as follows:     How can I calculate the columns Count 2020, Count 2019, by using the As of date column as a filter in a visual?   Ej: As of date:...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous ,

     

    Pbix as attached, hopefully works for you.

     

    Best Regards,

    Jay

     

    Community Support Team _ Jay Wang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago

Well, let's match up the parens:

 

Measure 2 =

VAR TheChosenOne = SELECTEDVALUE('Date'[Date])

return

sumx(

  filter(

    table,

    table[As of date]<=

       DATE(

         year(TheChosenOne)-1,

        month(TheChosenOne),

        day(TheChosenOne)

       )

  ),

  table[Count 2020]

)

 

Found it!

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Thank you for checking Greg_Deckler .

 

I got the following error in the output table: Can't display the visual:

 

MdxScript(Model) (133,44) Calculation error in message ('table'[Measure 2]: An argument of function 'DATE' has the wrong data type or the result is too large or too small.

 

I changed the value type to Whole number, and I'm still getting the error.

 

Can you please check?

 

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago
    Are you getting this with nothing selected in your Date slicer? Probably need to return some date value using the optional parameters for SELECTEDVALUE to return a default?
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      I'm getting the message with both date selected, and no date selected in the slicer.