Forum Discussion

RobX's avatar
RobX
Advocate I
5 years ago
Solved

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.  ...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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's avatar
amitchandak
Super User
5 years ago

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=pnAesWxYgJ8

  • RobX's avatar
    RobX
    Advocate I
    5 years ago

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