Forum Discussion
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: 2/1/2020 vs. 2/1/2019
Thank you!
14 Replies
- Measure1 = SUM([Count])
Measure2 = 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?
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])
Hi Anonymous ,
Try
C_Y = SUM('Table'[count]) L_Y = CALCULATE(SUM('Table'[count]),FILTER(ALL('Table'),'Table'[As of date]=EDATE(SELECTEDVALUE('Table'[As of date]),-12)))Result would be shown as below.
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.
Hi Anonymous .
My desired output is a table, so I'm getting a fixed value for the L_Y measure for each month.
The table is able to show
Count 2020= CALCULATE(sum(table[count]),table[sales date]>=date(2020,1,1),table[sales date]<=date(2020,12,31))
values in the output table, for January to December of this year
Thanks
Hi Anonymous ,
Check if the visual below is what you want.
C_Y = CALCULATE(SUM('Table'[count]),FILTER(ALLEXCEPT('Table','Table'[sales date month]),'Table'[as of date] in VALUES('Table'[as of date]))) L_Y = CALCULATE(SUM('Table'[count]),FILTER(ALLEXCEPT('Table','Table'[sales date month]),EDATE('Table'[as of date],12) in VALUES('Table'[as of date])))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.
