Quarter over Quarter variance measure/column for table with quarter end amounts by account.

[garrettmindrup]

I have a table that lists quarter-end account balances by account and business group and I'd like to create a measure or column that shows the quarter-over-quarter balance change for each company/account combo (e.g., Group A, Account 10000 quarter-over-quarter change is $500 in Q2).

I've tried various formulas using the calculate function, but these don't work if I don't apply aggregation (SUM and SUMX are what I've tried) to the AMOUNT column. The aggregations seem to mess up the results. I either get blank values or duplicate amount values. I think it's because the amounts don't need to be aggregated. They're already aggregated values.

How can I do a simple subtraction based on quarterly dates without having to aggregate my amount column?

The formula(and similar variations) I've tried:

VAR CurrentAmt = TABLE1[AMOUNT]
VAR PrevAmt = CALCULATE(SUM(TABLE1[AMOUNT]), PREVIOUSQUARTER(TABLE1[DATE].[DATE])
RETURN = CurrentAmt - PrevAmt

Table example:

Group	Account	Q End Amount	Quarter
A	100000	$2000	3/31/22
A	200000	$3000	3/31/22
B	100000	$4000	3/31/22
B	200000	$5000	3/31/22

[khtan518]

Hi @garrettmindrup  - Did you manage to resolve this? If so, could you please share the solution? 

[garrettmindrup]

Hi @khtan518 , I have not found a solution yet. 

[amitchandak]

@garrettmindrup , Always use date table marked as date table joined with your table

VAR CurrentAmt = TABLE1[AMOUNT]
VAR PrevAmt = CALCULATE(SUM(TABLE1[AMOUNT]), PREVIOUSQUARTER(Date[DATE])
RETURN = CurrentAmt - PrevAmt

Also do not use .Date

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
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

[garrettmindrup]

Thanks, @amitchandak. I tried your solution and did not get the result I was expecting. 

Here's what I did:

• Created a date table and marked it as a date table
• Created a relationship between the [Date] column in my date table and the [Quarters_Column] column in my accounts table (Originally ref'd as table1[Date] above). 
• Used the DAX formula you referenced, utilizing the [Date] field from the date table. 

Quarterly_Change = 
VAR PREVAMOUNT = CALCULATE(SUM(AccountBalances[Period Value in Group Currency]), PREVIOUSQUARTER(Date_Table[Date]))
VAR CURRAMOUNT = AccountBalances[Period Value in Group Currency]
RETURN CURRAMOUNT - PREVAMOUNT 

 
The results are returning the current quarter amount rather than the variance from the previous quarter. The below screenshot shows an example. I'm wanting it to calculate ($2,329,916.31) - $4,429,234.68 and return $(6,759,150.99). 

Any idea on what else I could try? 

Additional screenshots:
Date table:

Relationship between Amounts Table and Date Table: