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.
Community ChampionMeasure2 = SUMX(FILTER('Table',[As of Date] <= 0DATE(2019,2,1)),[Count])
Thank you Greg_Deckler
I was able to get the "As of date" field as a visual filter.
How to transform the measure 2, to get the value 1 year before the one I selected on the filter?
- Greg_Deckler
Measure2 =
VAR __TheChosenOne = SELECTEDVALUE('Table'[Date]) // this is your slicer
RETURN
SUMX(FILTER('Table',[As of Date] <= DATE(YEAR(__TheChosenOne) - 1,MONTH(__TheChosenOne),DAY(__TheChosenOne)),[Count])
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.
- Greg_Deckler
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!