Forum Discussion

wynhodgkiss's avatar
wynhodgkiss
Icon for Advocate II rankAdvocate II
4 months ago
Solved

Aligning 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 ...
  • Shai_Karmani's avatar
    4 months ago

    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