Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 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 eac...
Need Help
  • dedelman_clng's avatar
    dedelman_clng
    6 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

     

     

dedelman_clng's avatar
dedelman_clng
Icon for Community Champion rankCommunity Champion
6 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
    6 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
      6 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

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Got it, I will try a few things and see if I can get it to work. Thank you for your responses