topic Re: Quarter over Quarter variance measure/column for table with quarter end amounts by account. in DAX Commands and Tips

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

Anonymous — Tue, 14 Feb 2023 06:26:50 GMT

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

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

amitchandak — Tue, 14 Feb 2023 15:44:21 GMT

Anonymous , 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-bd52912a5bd4
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

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

Anonymous — Tue, 14 Feb 2023 20:22:46 GMT

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:

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

khtan518 — Sat, 18 Feb 2023 04:07:59 GMT

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

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

Anonymous — Mon, 20 Feb 2023 14:48:22 GMT

Hi khtan518 , I have not found a solution yet.