Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am needing help, please, and I'm very new to PBI so any help with explaining logic if solving this with DAX would be very much appreciated!
Below (Screen Shot #1) is a visualization showing customer invoices from month to month. I'm trying to figure out a way of referencing the previous month to see if there is either a + or - change of, say, 5% and, if so - the invoice amount is highlighted red. In this manner, a member of the sales team can quickly review the list of cusotmers for the current month and see, quickly, if there was a deviation from the previous month.
The two tables (I was told to create a date table to do the date comparisons) are in screenshots 2 & 3. The "AGR_Header_RecID is essentially the customer ID (as it's represented in an agreement table). The main key values in the table are:
Any help would be VERY much appreciated.
SCREEN SHOT #1
SCREEN SHOT #2
SCREEN SHOT #3
Solved! Go to Solution.
Hi @Anonymous ,
According to your description, I create a sample, you can create a measure like this:
Measure =
VAR _Pre =
MAXX (
FILTER (
ALL ( 'Table' ),
'Table'[Company_Name] = MAX ( 'Table'[Company_Name] )
&& YEAR ( 'Table'[Date] ) = YEAR ( MAX ( 'Table'[Date] ) )
&& MONTH ( 'Table'[Date] )
= MONTH ( MAX ( 'Table'[Date] ) ) - 1
),
'Table'[Invoice]
)
RETURN
IF (
_Pre <> BLANK ()
&& DIVIDE ( MAX ( 'Table'[Invoice] ) - _Pre, MAX ( 'Table'[Invoice] ) ) > 0.03,
"RED"
)
In the visual formatting pane>Cell elements, turn on the Font color, apply the measure to the Invoice color.
Get the result.
I attach my sample below for reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
According to your description, I create a sample, you can create a measure like this:
Measure =
VAR _Pre =
MAXX (
FILTER (
ALL ( 'Table' ),
'Table'[Company_Name] = MAX ( 'Table'[Company_Name] )
&& YEAR ( 'Table'[Date] ) = YEAR ( MAX ( 'Table'[Date] ) )
&& MONTH ( 'Table'[Date] )
= MONTH ( MAX ( 'Table'[Date] ) ) - 1
),
'Table'[Invoice]
)
RETURN
IF (
_Pre <> BLANK ()
&& DIVIDE ( MAX ( 'Table'[Invoice] ) - _Pre, MAX ( 'Table'[Invoice] ) ) > 0.03,
"RED"
)
In the visual formatting pane>Cell elements, turn on the Font color, apply the measure to the Invoice color.
Get the result.
I attach my sample below for reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
" I'm trying to figure out a way of referencing the previous month"
Power BI has no concept of "previous" or "next" column or row. You need to explain to the engine how your data is sorted and what the intervals are.
Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to assist you. Avoid posting screenshots of your source data if possible.
Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |