Forum Discussion
Advocate IIAligning different dates as M1, M2 etc
Hi,
I have a table with: Countries, Products, Attributes (eur, gbp, units etc), Sales & Dates. Each product was launched on different dates in different countries but my requirement is for a single chart to compare the launches, with sales on the y axis (with an Attribute slicer) , Month # on the x axis (Month 1 being launch month) and Country in the legend.
I can think of a slow way to do this in power query: 1 query per product & country combination, index column from 1 and then join them all together again but if anyone has a more elegant way of doing this in PQ or DAX it would be great.
For this kind of cohort/launch-aligned chart, the cleanest approach is a calculated column on the fact table that turns each row's date into "months since launch" for that Country and Product. Then put that column on the X axis, Country in the legend, and Sales on Y, with your Attribute as a slicer.
Months Since Launch = VAR LaunchDate = CALCULATE ( MIN ( 'Sales'[Date] ), ALLEXCEPT ( 'Sales', 'Sales'[Country], 'Sales'[Product] ) ) RETURN ( YEAR ( 'Sales'[Date] ) - YEAR ( LaunchDate ) ) * 12 + ( MONTH ( 'Sales'[Date] ) - MONTH ( LaunchDate ) ) + 1Replace 'Sales' with your table name. The launch month shows as 1, the next month as 2, and so on, no Power Query loops needed. If you want a clean "M1, M2, ..." label, add another column: M Label = "M" & 'Sales'[Months Since Launch] and use that on the axis.
If this works for you, kindly mark it as the solution and give a thumbs up.
Best,
Shai Karmani
3 Replies
- Shai_Karmani
Super User
For this kind of cohort/launch-aligned chart, the cleanest approach is a calculated column on the fact table that turns each row's date into "months since launch" for that Country and Product. Then put that column on the X axis, Country in the legend, and Sales on Y, with your Attribute as a slicer.
Months Since Launch = VAR LaunchDate = CALCULATE ( MIN ( 'Sales'[Date] ), ALLEXCEPT ( 'Sales', 'Sales'[Country], 'Sales'[Product] ) ) RETURN ( YEAR ( 'Sales'[Date] ) - YEAR ( LaunchDate ) ) * 12 + ( MONTH ( 'Sales'[Date] ) - MONTH ( LaunchDate ) ) + 1Replace 'Sales' with your table name. The launch month shows as 1, the next month as 2, and so on, no Power Query loops needed. If you want a clean "M1, M2, ..." label, add another column: M Label = "M" & 'Sales'[Months Since Launch] and use that on the axis.
If this works for you, kindly mark it as the solution and give a thumbs up.
Best,
Shai Karmani
wynhodgkissPerfect, that does the trick. Thanks for your help
- lbendlin
You may want to explore the recently announced unmaterialized calculated columns feature. This will give you the flexibility to do dynamic-ish bucketing.
