Max number of promotions based on selected time frame

[Nikola1]

Hi,

Fairly new to the world of PBI and Dax and I couldnt seem to find a solution for an issue i am running into. I am trying to get a promo count by brand using the max number of promtions that have been run during the selected timeframe (normally rolling 12 months).

As background. My data is set up by sku level with a column that has a 0 or 1 if that sku was on promotion for the selected month. Brand is mapped from a different table.

For example if Brand X has 5 skus under it and had different promo counts. I would like to know what is the max number of promotions this brand does as a whole within the year. 

Brand	Sku #	Jan	Feb	Apr	May	Jun	Jul	Aug	Sep	....	Total promo count R12
X	1	0	0	1	0	1	0	1	0	0	3
X	2	0	0	1	0	0	1	0	0	0	2
X	3	0	1	0	1	0	1	0	1	1	5
X	4	0	1	0	1	0	1	0	1	1	5
X	5	0	0	1	0	0	0	0	0	1	2

The result I would like to have is 5. Which is the max sum of promos within the R12 timeframe.

This will be in a filtered quadrant chart by brand with that max count and volume. So I would need it to recalculate if I choose R6 instead, or use other filters.

Please let me know if this is enough information.

edit:

Essentially the order of operations here would be to sum promo counts for the selected timeframe for each sku. Then find the max of this sum for each brand.

Thanks

[VahidDM · Accepted solution]

Hi @Nikola1 

Please download this file: https://gofile.io/d/y8KBzY

https://support.microsoft.com/en-us/office/unpivot-columns-power-query-0f7bad4b-9ea1-49c1-9d95-f5882...

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

Appreciate your Kudos!!

[Nikola1]

Thanks, this worked perfectly. I always forget that adding in new columns could help. I made a slight modification to the formula to capture the R12 months from the dataset just becuase my data is set up slighlty differently. But this Worked!!!