cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rsearing
Helper II
Helper II

Calculating Monthly Difference and Formatting Accordingly

@lbendlin I deleted the post you commented on and re-wrote it here as it would seem that this is a way to reference a previous column via a measure (might be mis-stating that, but conceptually - that's what's going on). I just don't understand what @tom480 did as, which I explain below, he references his own made up data and I don't quite understand the formula's he's posting (I'm quite new, sorry). Also, and again, please forgive my ignorance, I don't know how to link a PBI report into this thread and would need to know how to remove the SQL connection that is used to produce my Invoice table below as it has containes the connection details to my work's database. Praying you, or someone, can just help in fixing Tom's measure's below using the real tables I've pasted with just a brief description of the DAX please.

 

Good day! I started this conversation in this thread but it became extremely convoluted with different topics as the solution required use of creating a date table (which I have since been able to successfully resolve) however the other part of the conversation (which directly impacts the original quesiton) was lost in the mix and I was having difficulty understanding parts of it so thought I'd be open to either other suggestions or beg help in understanding what one of the authors, @tom480 had suggested. 

 

The Challenge: I have a table created that pulls in invoices from various customers (Screen Shot 2). I have a date table that I had help in building (Screen Shot 3). It is displayed in a matrix nicely (Screen Shot 1). What I am trying to do is highlight the "next" month if it there is greater than a 5% change from their previous month's invoice (either up or down) so that a sales person can figure out why. In the proposed solution, it was suggested the following:

 

The first measure (that you already have) is in my case this one: 

(My note: I assumed this measure to be SUM ( 'Invoice Data'[Montly_Inv_Amt]) but, I am having difficulty understanding his logic His two tables used as an example are below (Screen Shots 4 & 5)

ValueMeasure = SUM ( TableA[Value] )

Second, we need another measure that calculates the difference (%) between each month. I just created this one quickly, but pressumably you need to calculate it differently. The point of my reply is not showing how to calculate such a differences but more how you can use such a measure for color coding:

(My note: IThis is where I get really confused.

  1. His TableB, which I assume is meant to be the date table (TableDT) as a "DateIndex" field. The DateTable doesn't and I'm not sure what to reference.
  2. This is completely my ignorance, but I am NOT savvy with DAX and not understanding what the CALCULATE functions are doing here, nor how to relate them to my 'actual' tables. (I feel horrible, but understanding what these functions are doing and re-writing them as they relate to my two tables (Screen shots 2 & 3) would be VERY helpful/appreciated.
  3. I somewhat understand what he's doing in DIVIDE (subtracting the difference and dividing that by the amount of previous month to get a percentage, but the "+0" confused me.
ValuesMeasureDiff% = 
VAR _valueCurrentMonth = CALCULATE ( [ValueMeasure], TableB[DateIndex] = MAX ( TableB[DateIndex] ) )
VAR _valuePreviousMonth = CALCULATE ( [ValueMeasure], ALLEXCEPT(TableA, TableA[Type] ), TableB[DateIndex] = MAX ( TableB[DateIndex] ) - 1 ) 
RETURN 
DIVIDE (_valueCurrentMonth - _valuePreviousMonth, _valuePreviousMonth ) + 0

Where to put the measures then, within the matrix visualization, (in other words, where to go from there) is also confusing. 

 

If there is a better/different way of doing this, I'm open...but, and it pains me deeply to say this, I'm new to PowerBI so explaining (high level) what a function is doing (more so that just inserting it there) or, at least, helping me by using real values from the tables I pasted below would be VERY helpful. I am NOT trying to make light of Tom's assistance, he truly went above and beyond. But I'm simply having difficult understanding his measures - especially when it's not using columns from my tables.

 

Screen Shot 1 - Where I'm at now

rsearing_0-1653245444630.jpeg

 

 

Screen Shot 2 - The Invoice Data table

rsearing_1-1653245444308.png

 

 

Screen Shot 3 - The TableDT table

rsearing_2-1653245444309.png

 

 

Screen Shot 4 - His "TableA" used in his example

rsearing_3-1653245444310.png

 

 

Screen Shot 5 - His "TableB" used in his example

rsearing_4-1653245444760.png

 

 

2 REPLIES 2
v-shex-msft
Community Support
Community Support

Hi @rsearing,

Any update for these? Did the above suggestion help with your scenario? if that is the case, you can consider Kudo or accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
tamerj1
Super User
Super User

Hi @rsearing 
I sent you eamil. Please check

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show Episode 10 Recap

The Power BI Community Show

Watch the playback when Amit Chandak, a Power BI Super User, demos how to use Field Parameters to make reports more dynamic.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.

Top Solution Authors
Top Kudoed Authors