Create Measure Filtered on Dates in Two Tables.

[RobX]

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

[Anonymous · Accepted solution]

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.

[amitchandak]

@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-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8

[RobX]

That doesn't appear to work.  I created a relationship between Sales and Calendar on SaleDate = CalDate.