Aligning different dates as M1, M2 etc

[wynhodgkiss]

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.

[lbendlin]

You may want to explore the recently announced unmaterialized calculated columns feature. This will give you the flexibility to do dynamic-ish bucketing.

[Shai_Karmani · Accepted solution]

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 ) ) + 1

Replace '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

[wynhodgkiss]

Perfect, that does the trick. Thanks for your help