Visual to display 2 months of data in table and show Trend.

[powerBIN00B16]

hello, trying my luck who can notice my post:

This is my desired output: (the month must be merged with Groups A and B)

	Jan-2024		March 2024
Items	Group A	Group B	Group A	Group B	Trend
Pencils	90	500	100	900	down
Erasers	100	400	500	250	down

The visual will always show 2 months and will compare the average quantity per month and will show if the Trend is going up or down. If latest month is higher than previous month, it's up, if lower then down, if the same then equal.

Also, I need to have a dynamic filter to choose the month. If I choose March, the visual will display Jan and March. If I choose January, the visual will show January and November from previous year.

I was able to create the visual using DAX in columns, but not successful in implementing a dynamic filter. If I use a parameter, I am not able to use the selected value in the parameter in my DAX formula in columns. 

Hope to get someone advise me what to do. Thank you in advance.

My data has the following columns: Date, Group, Items, Quantity.

Sample Data:

Date	Group	Item	Quantity
1/1/2024	Group A	Erasers	100
1/2/2024	Group A	Pencils	90
1/3/2024	Group B	Erasers	400
1/4/2024	Group B	Pencils	500
2/1/2024	Group A	Erasers	50
2/2/2024	Group A	Pencils	70
2/3/2024	Group B	Erasers	150
2/4/2024	Group B	Pencils	200
3/1/2024	Group A	Erasers	500
3/2/2024	Group A	Pencils	100
3/3/2024	Group B	Erasers	250
3/4/2024	Group B	Pencils	900

[Anonymous · Accepted solution]

Hi ALL,
Firstly  lbendlin thank you for your solution!
And @powerBIN00B16 ,I think you here is the need for a Group and date filter to control the filtering right, according to your needs, we can select multiple dates, but only compared to the maximum date we selected and the minimum date, the value in the middle of the filtered out by us, I hope that our dax help you!

Trend Measure = 
VAR MinData = MINX(ALLSELECTED('Table'), 'Table'[Date])  
VAR MaxData = MAXX(ALLSELECTED('Table'), 'Table'[Date]) 
VAR CurrentDate = SELECTEDVALUE('Table'[Date])  
VAR Group_MinMonth_Quantity = 
CALCULATE(
    SUM('Table'[Quantity]),
    'Table'[Date] = MinData,  -- Calculate quantity for the min selected month
    'Table'[Group] IN VALUES('Table'[Group])  -- Keep the group context
)

VAR Group_MaxMonth_Quantity = 
CALCULATE(
    SUM('Table'[Quantity]),
    'Table'[Date] = MaxData,  -- Calculate quantity for the max selected month
    'Table'[Group] IN VALUES('Table'[Group])  -- Keep the group context
)

VAR Result = 
IF(
    Group_MaxMonth_Quantity > Group_MinMonth_Quantity, 
    "up", 
    IF(Group_MaxMonth_Quantity < Group_MinMonth_Quantity, 
    "down", 
    IF(MONTH(MAX('Table'[Date]))<>MONTH(MinData)||MONTH(MAX('Table'[Date])<>MONTH(MaxData)),BLANK(),
    "equal")
)
)
RETURN
Result

I hope this dax can solve your problem, if you have further questions, you can feel free to contact me, I will receive your message after the first time to reply to you, look forward to your reply!

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Also, I need to have a dynamic filter to choose the month. If I choose March, the visual will display Jan and March

Why November and not December?  It will be easier to let the user pick any two months and calculate the trend from that.

In your example the trend goes down.  Why?  March numbers are higher than January numbers?

[powerBIN00B16]

yeah, my bad, the trend for March should be "up". 

well probably I can suggest to choose 2 months, but what if the user chooses 3 months? how can I that they can only choose 2 months? sorry I am new to this. thanks for your reply. 

[lbendlin]

they can choose as many months as they want, the measure would only calculate the trend between the first and last selected ones.

[powerBIN00B16]

I see okay, sounds good. can you help me how to do it? also, the groups and months are in columns too, how can I make that the trend will now show up per month and per group?