Show values on Matrix based on range dimension

[Anonymous]

Hi, i need help with this visual

I have this matrix 

Month	-3	-2	-1	0	1	2	3	Total
January	2.3	2.2	2.5	1.2	2.7	2.6	2.9	6.9
February	2.2	2.5	2.4	1.6	2.6	2.5		5.4
March	2.3	2.7	2.4	1.4	2.4			4.1
Total	2.7	2.6	2.3	1.5	2.5	2.4	2.9	6.8

But i need to present it this way

Month	-3	-2	-1	0	1	2	3	Total
January	2.3	2.2	2.5	1.2	2.7	2.6	2.9	6.9
February		2.5	2.4	1.6	2.6	2.5		5.4
March			2.4	1.4	2.4			4.1
Total	2.3	2.3	2.3	1.5	2.5	2.4	2.9	6.8

There are 2 dimensions, Months calendar and Harvest range (-3 to 3) wich is the periods before and after the month and 0 being the base month. And one measure, customer average transactions.

In order to evaluate the customer behaviour, the variation must be calculated using the same harvest range in each month. For example for February we must use the values (2.5, 2.4) vs (2.6, 2.5) wich is the range -2 to 2. 

I am trying to do this, but with no luck

[Average Transaction] = DIVIDE(SUM(FACT_TRANSACTION_GROWTH[TRX]),DISTINCTCOUNT(FACT_TRANSACTION_GROWTH[CUSTOMER]))

MaxHarvest = 

         var Harvest = CALCULATE(MAX(HARVEST[MONTHS]), HARVEST[DAYS] = MAX(FACT_TRANSACTION_GROWTH[DAYS])) - 1

         return IF(Harvest > 0, Harvest)

"I am substracting 1 because, the last month doesn't always have complete days."

Measure = IF(SELECTEDVALUE(HARVEST(MONTHS) >= MaxHarvest*-1 &&  SELECTEDVALUE(HARVEST(MONTHS) <= MaxHarvest,[Average Transaction] )))

This only result in this

Month	0
January	1.2
February	1.6
March	1.4
Total	1.5

[Anonymous · Accepted solution]

Solved it;

Solution:

1st - Create a measure to calculate the maximun month to harvest

Max Harvest =

var Months= CALCULATE(MAX(HARVEST[MONTHS]),HARVEST[DAYS] = MAX(FACT_TRANSACTION_GROWTH[DAYS]))

return IF(Months> 0,Months)

2nd - Create same measure, but remove harvest filters

Max Harvest Fixed = CALCULATE([Max Harves],REMOVEFILTERS(HARVEST))

3rd - Create a measure to calculate the minimun month to harvest

Min Harvest =

var Months= CALCULATE(MIN(HARVEST[MONTHS]),HARVEST[DAYS] = MAX(FACT_TRANSACTION_GROWTH[DAYS]))

return IF(Months < 0 && Months > [Max Harvest Fixed] * -1,Months)

4th - Create the final measure

Measure  =

var Months = CALCULATE(MAX(HARVEST[MONTHS]),HARVEST[DAYS] = MAX(FACT_TRANSACTION_GROWTH[DAYS]))

return IF(Months >= [Min Harvest] && Months < [Max Harves Fixed],[Average Transaction])

The visual: 

[parry2k]

@Anonymous can you share the sample data with the expected output?

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[Anonymous]

This is the fact Table

Month	Customer	Days	Amount
January	A	0	3
January	A	-13	6
January	A	-65	5
January	A	-84	5
January	A	-65	4
January	A	58	2
January	B	0	1
January	B	49	5
January	B	-92	1
January	B	-78	3
January	B	-86	6
January	B	-56	3
January	C	0	6
January	C	15	6
January	C	-5	6
January	C	68	3
January	C	-57	3
January	C	-96	5

And this is the dimension table 

Days	Months
-42	-2
-41	-2
-29	-1
-28	-1
-27	-1
-26	-1
-25	-1
-24	-1
-23	-1
-22	-1
0	0
1	1
2	1
3	1
4	1
5	1
6	1
7	1
8	1
9	1
10	1
11	1