Forum Discussion

macemit's avatar
macemit
Frequent Visitor
7 years ago

Calculation Help with growing column

Hi,

 

Another calculation quandry.  It'll help if you see a sample first:

Release Date:   
May-18   
 Months Since Release (M)Fails (F)M x F
(May)51260
(June)41560
(July)32266
(August)236
(September,now)12525
    
   217


It is currently Sept, a product was released in May. That is a 5 month span. Each month since release must be multiplied by failures from that month (counted rows from our database). The example above explains it best. Table can grow indefinitely (in Sept 2019, it'd be a 17 month span, etc..)

 

The '217' is the sum of the right hand column. That is the only number I'm interested in. In fact, the fails (middle column) do not even need to be visible as distinct numbers, they can be summed to give a single number (77 in this case) if that would make calculating easier. I am hoping that this calculation can possibly be acheived through a single 'function', or measure without resorting to a new, growing table. 

 

I have to do this for multiple products, so obviously the release month will usually vary.

Thanks.

Need Help

4 Replies

  • PattemManohar's avatar
    PattemManohar
    Community Champion
    7 years ago

    Give a try with this please...

     

    GrandTotal = SUMX(MonthsFails,MonthsFails[Months Since Release (M)]*MonthsFails[Fails (F)])

    • macemit's avatar
      macemit
      Frequent Visitor
      7 years ago

      Thanks. That helps in part. However, is there a method of deriving the months since release without resorting to its own column? Or resorting to columns at all. Ideally, I'd like to have only 2 variables: the release month and total failures. Surely there is a formula that will not require a new table or column.

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    7 years ago

    Hi macemit,

     

    I made one sample for your reference. If it doesn't meet your requirement, kindly share your sample data or pbix to me.

     

    1. Enter the sample data and create a calculated column in the table.

     

    Month = FORMAT(RELAEASE[date],"mmmm")

    2. Create the measures as below.

     

    Months Since Release = DATEDIFF(MAX(RELAEASE[date]),TODAY(),MONTH)+1
    M = [Months Since Release]*SUM(RELAEASE[Fail])
    M x F = SUMX(RELAEASE,[M])

     

    For more details, please check the pbix as attached.

     

    Regards,

    Frank

    • v-frfei-msft's avatar
      v-frfei-msft
      Community Support
      7 years ago

      Hi macemit,

       

      Does that make sense? If so, kindly mark my answer as a solution to close the case.

       

      Regards,
      Frank