The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
I have a table that has a corn measure total column and a bean measure total column which then adds the two together and goes into a grain total column. I have a slicer setup to view the information by month. If I two months selected to compare the two, the total adds the two months together instead of subtracting to find the difference. I have tried the quick measure tool with no success and I have looked over many forums with no luck. i might not be understanding the formula's that i have found.
Solved! Go to Solution.
Hi @jbenson ,
Looks like you need to create a month number column first in order to compare, text month column could not be used to compare if you do not have a date column.
MonthNo. =
SWITCH (
'Table'[Month],
"JAN", 1,
"FEB", 2,
"MAR", 3,
"APR", 4,
"MAY", 5,
"JUN", 6,
"JUL", 7,
"AUG", 8,
"SEPT", 9,
"OCT", 10,
"NOV", 11,
"DEC", 12
)
Use the monthNo. column as a slicer and create a measure like this to calculate the difference:
Difference =
VAR maxmonth =
CALCULATE (
SUM ( 'Table'[Total Grain Measure] ),
FILTER (
'Table',
'Table'[MonthNo.]
= CALCULATE ( MAX ( 'Table'[MonthNo.] ), ALLSELECTED ( 'Table' ) )
)
)
VAR minmonth =
CALCULATE (
SUM ( 'Table'[Total Grain Measure] ),
FILTER (
'Table',
'Table'[MonthNo.]
= CALCULATE ( MIN ( 'Table'[MonthNo.] ), ALLSELECTED ( 'Table' ) )
)
)
RETURN
maxmonth - minmonth
Attached a sample file in the below, hopes to help you.
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @jbenson ,
Looks like you need to create a month number column first in order to compare, text month column could not be used to compare if you do not have a date column.
MonthNo. =
SWITCH (
'Table'[Month],
"JAN", 1,
"FEB", 2,
"MAR", 3,
"APR", 4,
"MAY", 5,
"JUN", 6,
"JUL", 7,
"AUG", 8,
"SEPT", 9,
"OCT", 10,
"NOV", 11,
"DEC", 12
)
Use the monthNo. column as a slicer and create a measure like this to calculate the difference:
Difference =
VAR maxmonth =
CALCULATE (
SUM ( 'Table'[Total Grain Measure] ),
FILTER (
'Table',
'Table'[MonthNo.]
= CALCULATE ( MAX ( 'Table'[MonthNo.] ), ALLSELECTED ( 'Table' ) )
)
)
VAR minmonth =
CALCULATE (
SUM ( 'Table'[Total Grain Measure] ),
FILTER (
'Table',
'Table'[MonthNo.]
= CALCULATE ( MIN ( 'Table'[MonthNo.] ), ALLSELECTED ( 'Table' ) )
)
)
RETURN
maxmonth - minmonth
Attached a sample file in the below, hopes to help you.
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello @jbenson
you have to create a date-table, create a relationship with your fact-table and create measure that makes the sum of the month in your current context and subract the sum of the month of your previous month ot your current context. I'm not a DAX expert, but this could lead you in the right direction.
CurrentMinusprevMonth = Sum('Table'[Value])-CALCULATE(
SUM('Table'[Value]),
PREVIOUSMONTH('date'[Date])
)
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
Hi @jbenson
Show a sample of your data and the expected result. Include the code of your current relevant measures
|
Please accept the solution when done and consider giving a thumbs up if posts are helpful. Contact me privately for support with any larger-scale BI needs, tutoring, etc. |
My corn measure and my bean measure are added to the total grain by month. most of the months are the same at this time because it is a new model so i just copied the same data over for each month. But when i have both December and January selected it will add them together instead of subtracting the difference. I haven't found a good code to use so i don't have any