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.

     

Anonymous's avatar
Anonymous
Not applicable
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.