Append table with column from calculateTable.

[robertvdleeuw]

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.

[amitchandak · Accepted solution]

@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

[robertvdleeuw]

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:

[robertvdleeuw]

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