topic Append table with column from calculateTable. in DAX Commands and Tips

Append table with column from calculateTable.

robertvdleeuw — Thu, 01 Sep 2022 13:10:22 GMT

I have the following table. I want use it to calculate the difference between the revenue of a given date, and that same date but one year ago, as to eventually create a visual in which I compare the revenue of Aug 2022 to Aug 2021, for instance.

I have the following DAX expression for this:

Revenue Growth =

VAR prevDate = DATEADD('Growth Metrics'[Date], -1, YEAR)

VAR diff =

SELECTCOLUMNS(

CALCULATETABLE('Growth Metrics', 'Growth Metrics'[Date] = prevDate),

"Date", 'Growth Metrics'[Date],

"Revenue Growth", 'Growth Metrics'[Revenue] - [Revenue])

RETURN

diff['Revenue Growth']

The problem is that I cannot access 'Revenue Growth' from diff like this. How can I append the existing table with this column?
Date is added in there because I tried using LOOKUPVALUE(), but to no avail.

Re: Append table with column from calculateTable.

amitchandak — Thu, 01 Sep 2022 15:18:44 GMT

robertvdleeuw , with help from date table you can have measure like

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))

diff = SUM(Sales[Sales Amount]) - [Year behind Sales]

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

Re: Append table with column from calculateTable.

robertvdleeuw — Thu, 01 Sep 2022 16:38:11 GMT

Hey, thanks for the help. The measure seems to be working reporting the right data, (essentially all sales - all of the most recent year). However, the measure reverts to (BLANK) once I try to select a daterange using a slicer.

Perhaps I should've made my goal a bit clearer. I currently have the table below to show changes in revenue each month and year, but I want to change this to revenue growth in comparison to last year.

Is there a way to get this done using this measure? Right now, using the measure shows this:

Re: Append table with column from calculateTable.

robertvdleeuw — Fri, 02 Sep 2022 12:54:28 GMT

Nevermind, I didn't have any type of link established between 2 date columns in separate tables. The measure works perfectly now!