Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Create a Launch Calendar Based on a Single Launch Date

I have a model that consists of a fact table that includes individual orders, a date table, and an Item Master table that includes all items and includes a column that defines the first date that each item was made available. The Item Master and Calendar are connected to the fact table with 1 to many relationships.

 

What I am hoping to do is to show how each item performed since their launch date, hopefully by using some kind of relative date on the axis of a visual. So, for example, if an item launches in July of 2019, I would like that to show as 0 and then have labels for each subsequent month. Is it possible to do something like this?

Need Help
  • dedelman_clng's avatar
    dedelman_clng
    5 years ago

    Hi Anonymous - on the table with the order data, create a calcaulated column as "Days since Launch"

     

    "Days since Launch" = DATEADIFF( RELATED ( "Item Master"[Launch Date] ), Order[Order Date], DAY)

     

    Use that column as your X-axis, and the product ID / name as your legend. You'll get something like this:

     

     

    Hope this helps

    David

     

     

4 Replies

  • dedelman_clng's avatar
    dedelman_clng
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hi Anonymous - on the table with the order data, create a calcaulated column as "Days since Launch"

     

    "Days since Launch" = DATEADIFF( RELATED ( "Item Master"[Launch Date] ), Order[Order Date], DAY)

     

    Use that column as your X-axis, and the product ID / name as your legend. You'll get something like this:

     

     

    Hope this helps

    David

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      dedelman_clng This does seem to work for all of the measures that sum up value columns on my fact table, which is great. I had also set up measures to calculate Rolling 12 month totals based off of this formula that I had found somewhere:

      Rolling 12 Month Units = 
    CALCULATE(
        [Total Units],
        FILTER(
            ALL( 'Calendar' ),
            AND(
                'Calendar'[Date] <= MAX( 'Calendar'[Date] ),
                DATEADD( 'Calendar'[Date], 1, YEAR ) > MAX( 'Calendar'[Date] )
            )
        )
    )

      It looks like this does not work with the Months Since Launch, would you have any suggestions for a better formula? I tried changing all of the dates in the formula to the connected column from the fact table, but it doesn't look like that is calculating correctly.

      • dedelman_clng's avatar
        dedelman_clng
        Icon for Community Champion rankCommunity Champion
        5 years ago

        Hi Anonymous  - you can still use the Days Since Launch in this regard. Just think of 12 months as 365 days. Something like the below:

         

        Rolling 12 Month Units = 
    CALCULATE(
        [Total Units],
        FILTER(
            ALLEXCEPT( Items, Items[Item Number] ), //We are calculating per item
            AND(
                Orders[Days Since Launch] <= MAX( Orders[Days Since Launch] ),
                Orders[Days Since Launch] > MAX( Orders[Days Since Launch] )-365
            )
        )
    )

         

        The ALLEXCEPT may need to change based on how you want to be showing the "Rolling 12" and possibly based on how [Total Units] is coded.  If the above doesn't work, please share a pbix so I can be sure to use your tables and your model.

         

        Hope this helps

        David