Rolling 12 Percent Change Grouped by Customer

[Anonymous]

Please help! I've been working with this for awhile and I cannot figure it out.

My company utlizes Rolling 12 Months for statistics. I grouped my data using these months to determine year (i.e., August-September for 2018-2019 would be year 12 and August-September of 2019-2020 would be year 13). I was also able to determine the total sales for each year. Further, I can determine for each business, what their total sales were for the rolling 12 year by grouping the rows. However, when I try to determine the percent change of each of these customers between rolling 12 years, I am unable to get it done.

I have tried calculated columns, but I get a circular reference error. I've tried creating new tables, but my relationships are not accurate then. Am I missing something? Can someone who is DAX savvy help me out? This has been an open topic for the database for awhile now.

Here's a sample of what I am looking for. Formatting can change as needed because I know that it will be easier to code subtraction of 2 columns rather than rows, but this is the information that I need:

YearLabel	Roling12Year	CustomerName	TotalSales	SumDifference	PercentChange
Year 13	2019-2020	Customer A	$100,000	$50,00	50%
Year 12	2018-2019	Customer A	$50,000
Year 13	2019-2020	Customer B	$500,000	$100,000	25%
Year 12	2018-2019	Customer B	$400,000

[amitchandak]

@Anonymous , Take our rolling year into a new table and create a rank on that

column in year Table

Year Rank = RANKX(all('Year '),'Year '[rolling year],,ASC,Dense)

New measures
This Year = CALCULATE(sum('Table'[TotalSales]), FILTER(ALL('Year '),'Date'[Year Rank]=max('Year '[Year Rank])))
Last Year = CALCULATE(sum('Table'[TotalSales]), FILTER(ALL('Year '),'Date'[Year Rank]=max('Year '[Year Rank])-1))

[DataInsights · Accepted solution]

@Anonymous,

Try this approach. I recommend adding YearNum (e.g., 12, 13) to your date table; this will enable you to calculate more easily. The date table should be joined to the fact table.

Measures:

Total Sales = SUM ( RollingTest[Sales] )

PY Sales = 
VAR vYearNum =
    MAX ( DateTest[YearNum] )
VAR vResult =
    CALCULATE ( [Total Sales], FILTER ( ALL ( DateTest ), DateTest[YearNum] = vYearNum - 1 ) )
RETURN
    vResult

Percent Change = DIVIDE ( [Total Sales] - [PY Sales], [PY Sales] )

Output: