Forum Discussion

neeharikathota's avatar
neeharikathota
Frequent Visitor
6 years ago

Product trend analysis

Hi all , 

 

I have to show product SWAP rate analysis based on the launch date per each product .

 

Need to show the total SWAP happened for each product for last n months (4 months , 8 months , 12 months) . In this case the launch month per each product is different(dynamic) but I need to show the first 4 months (or 8 months / 12 months ) SWAP trends for the last n months for all the products using a line graph . 

 

Please help on how to do it using calculated columns , I have SWAp rates for the products and launch month per each product . 

 

Thanks .

PowerQuestion Calcul8or PowerQueryFTW 

Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    I don't think you need calculated columns.  If you have a product table, hopefully you can a column already in your data with the launch date.

     

    Next you need some measures that will calculate what you need, which can do the time related math based on that date.  The measure would be designed to be used in a context where you are displaying information related to products, i.e. a table with each product listed,  or a graph with 1 row per product.

     

    Here is a quick example that assumes:

    • You have a date table called "Dates" with a date column called "Date"
    • You have a product table called "Product" with a column called "LaunchDate"
    • You have a SWAP rate measure already
    Swap Rate 4 Months= var launchDate = FIRSTDATE('Product'[LaunchDate])
var result = CALCULATE(
	[SWAP],
	ALL('Dates'),
	DATESINPERIOD('Dates'[Date], launchDate, 4, MONTH)
)
RETURN
result

     

    • neeharikathota's avatar
      neeharikathota
      Frequent Visitor
      6 years ago

      Thanks a lot for the response Anonymous  . I will try to implement the SWAP Rate for 4months as suggested by you and get back for any clarifications . 

       

      Thanks 

      Neeharika.

       

    • neeharikathota's avatar
      neeharikathota
      Frequent Visitor
      6 years ago

      Hi Anonymous , 

       

      Now i have to save the monthly SWAP rates based on the launch date and should be able to show the SWAP rate values as below . 

       

      X - Axis : The SWAP rates for the products for first month , first 2 months , first 3 months .... first n months for the products .

      Y Axis : SWAP rate percentage which I already have . 

      As already mentioned I have the launchdates of each product as well . Can you suggest how can I achieve the X - axis now ? 

       
       

       

      Thanks in advance . 

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Since we have a SWAP formula that can calculate any date range, you would just need to use the Date Table's Month/Year values on the X axis.  You could use a measure like this to calculate the SWAP value to date.  The X Axis will ensure you are getting each month

         

        Swap Rate To Date = var metricDate = LASTDATE('Dates'[Date])
var result = CALCULATE(
	[SWAP],
	ALL('Dates'),
	'Dates'[Date] <= metricDate
)
RETURN
result

         

        If you want a variable start date, you'll need to add a little bit more to the code.