Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Variance Minus 1 month

Hello, I'm a beginner DAX/Power Bi and am stuck as to why my logic does not work or what the correct logic should be. I'm trying to find the variance (and then percentage of variance) from month to month of a dynamic 3 month average. Bascially did the average go up or down from the previous month by how much and what percent.

 

Here is what I have so far:

Variance = CALCULATE(SUM('ARTransactiontbl'[Positive Amount]),DATESINPERIOD('Date'[Date],LASTDATE('Date'[Date]),-3,MONTH))/3
-
DATEADD(CALCULATE(SUM('ARTransactiontbl'[Positive Amount]),DATESINPERIOD('Date'[Date],LASTDATE('Date'[Date]),-3,MONTH))/3,-1,MONTH)

Pic3.JPG

4 REPLIES 4
v-juanli-msft
Community Support
Community Support

Hi @Anonymous

In your formula, DATEADD is used uncorrectly.  please read reference here to learn the usage.

Variance = CALCULATE(SUM('ARTransactiontbl'[Positive Amount]),DATESINPERIOD('Date'[Date],LASTDATE('Date'[Date]),-3,MONTH))/3
-
DATEADD(CALCULATE(SUM('ARTransactiontbl'[Positive Amount]),DATESINPERIOD('Date'[Date],LASTDATE('Date'[Date]),-3,MONTH))/3,-1,MONTH)

Based on my understanding, 

"a dynamic 3 month average" means, eg, 

for 2018/10, avg=(2018/8+2018/9+2018/10)/3

for 2018/11, avg=(2018/9+2018/10+2018/11)/3

for 2018/12, avg=(2018/10+2018/11+2018/12)/3

 

then the percentage of variance should be 

for 2018/10, (avg-2018/9)/2018/9

for 2018/11, (avg-2018/10)/2018/10

for 2018/12, (avg-2018/10)/2018/10

 

Is my understanding right?

 

Best Regards

Maggie

 

 

 

Anonymous
Not applicable

Hi Maggie,

 

I forgot to respond to your understanding - You are absolutely correct in your math.

 

Tom

Anonymous
Not applicable

Hi Maggie,

 

I though my use was correct. Your reference says: DATEADD(<dates>,<number_of_intervals>,<interval>)

 

My assupmtion: 

<dates>: (CALCULATE(SUM('ARTransactiontbl'[Positive Amount]),DATESINPERIOD('Date'[Date],LASTDATE('Date'[Date]),-3,MONTH))/3

 

<number_of_intervals>: -1

 

<interval>: MONTH

 

Is my assumption incorrect because I have the Calculated Sum as part of the <date>?

 

Sincerely,

Tom

Greg_Deckler
Super User
Super User

See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.