Forum Discussion
Create Measure Filtered on Dates in Two Tables.
I have two tables. One calendar table and one sales table. I need to create a current year sales measure and a prior year sales measure so that I can compare the sales results on a weekly basis.
Below are the two tables and the SELECT query is the results I want in Power BI. How can do I write the filter for a measure that gives me the results below?
--==== Sample data
Declare @Calendar Table (CalDate DATE, CalWeek INT)
Insert Into @Calendar (CalDate, CalWeek)
Values ('1/1/2021', 1)
, ('1/2/2021', 1)
, ('1/3/2021', 1)
, ('1/4/2021', 1)
, ('1/5/2021', 1)
, ('1/6/2021', 1)
, ('1/7/2021', 1)
, ('1/8/2021', 2)
, ('1/9/2021', 2)
, ('1/10/2021', 2)
, ('1/11/2021', 2)
, ('1/12/2021', 2)
, ('1/13/2021', 2)
, ('1/14/2021', 2)
, ('1/15/2021', 3)
, ('1/16/2021', 3)
, ('1/17/2021', 3)
, ('1/18/2021', 3)
, ('1/19/2021', 3)
, ('1/20/2021', 3)
, ('1/21/2021', 3);
Declare @Sale Table (SaleDate DATE, Amount INT)
Insert Into @Sale (SaleDate, Amount)
Values ('1/1/2020', 1)
, ('1/2/2020', 3)
, ('1/9/2020', 1)
, ('1/16/2020', 5)
, ('1/2/2021', 2)
, ('1/5/2021', 4)
, ('1/10/2021', 2)
, ('1/17/2021', 8)
, ('1/18/2021', 6);
SELECT
c.CalWeek
, SUM(scy.Amount) AS CYSales
, SUM(spy.Amount) AS PYSales
FROM @Calendar c
LEFT JOIN @Sale scy ON c.CalDate = scy.SaleDate
LEFT JOIN @Sale spy ON c.Caldate = DATEADD(yy, 1, spy.SaleDate)
GROUP BY c.CalWeek
Hi RobX ,
Based on your description, you can create a calculated table as follows.
Case =
var x1=SUMMARIZE(FILTER(CROSSJOIN('Calendar','Sale'),[CalDate]='Sale'[SaleDate]),[CalWeek],"CYSales",SUM(Sale[Amount]))
var x2=SUMMARIZE(FILTER(CROSSJOIN('Calendar','Sale'),[CalDate]=DATEADD('Sale'[SaleDate].[Date],1,YEAR)),[CalWeek],"PYSales",SUM(Sale[Amount]))
return
NATURALLEFTOUTERJOIN(x1,x2)Result:Hope that's what you were looking for.
Best Regards,
Yuna
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
RobX , This year would be simple like
SUM(Sales[Sales Amount])
for last years you can use date table and time intelligence
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))if needed refer
Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8That doesn't appear to work. I created a relationship between Sales and Calendar on SaleDate = CalDate.
Hi RobX ,
Based on your description, you can create a calculated table as follows.
Case =
var x1=SUMMARIZE(FILTER(CROSSJOIN('Calendar','Sale'),[CalDate]='Sale'[SaleDate]),[CalWeek],"CYSales",SUM(Sale[Amount]))
var x2=SUMMARIZE(FILTER(CROSSJOIN('Calendar','Sale'),[CalDate]=DATEADD('Sale'[SaleDate].[Date],1,YEAR)),[CalWeek],"PYSales",SUM(Sale[Amount]))
return
NATURALLEFTOUTERJOIN(x1,x2)Result:Hope that's what you were looking for.
Best Regards,
Yuna
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
