Forum Discussion
Comparing current vs last year sales - multiple date columns
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.
I calculated Count 2020 with the measure (no date table needed so far):
Count 2020= CALCULATE(sum(table[count]),table[sales date]>=date(2020,1,1),table[sales date]<=date(2020,12,31))
If I select 2/1/2020 "As of date" visual filter, Count 2020 works in the output table.
The measure 2, using a data table looks as:
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]))
Unfortunately for Measure 2 I got the error: too many arguments were passed to the FILTER function. The maximum argument count for the function is 2.
Community ChampionWell, 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!
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_DecklerAre 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?
I'm getting the message with both date selected, and no date selected in the slicer.