Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Calculated Column Self Referencing Itself at Prior Period

For a little bit of background on the problem, I have monthly data with various products. Products can span many months and many products can be active during the same months. Each product has a fiel...
Need Help
  • v-janeyg-msft's avatar
    v-janeyg-msft
    4 years ago

    Hi, Anonymous 

     

    Powerbi doesn't support recursion, but your needs are not completely recursive, it should be supported.

    How about this:

    Allocated Quantity =
VAR TOTAL =
    SUMX (
        FILTER (
            Table,
            [Date] <= EARLIER ( Table[Date] )
                && [Product] = EARLIER ( Table[Product] )
        ),
        [Monthly Quantity to Assign]
    )
VAR maxquantity =
    MAXX (
        FILTER ( Table, [Product] = EARLIER ( Table[Product] ) ),
        [Max Quantity]
    )
RETURN
    IF ( TOTAL > maxquantity, 0, TOTAL )

     

    Best Regards,
    Community Support Team _ Janey
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Thank you for the response. I need it to be running sum prior to this month and at the product level. I can manipulate the code you provided to get that.

sumx(filter(Table, [Date] < earlier([Date]) && [Product] = Earlier([Product])) , [Allocated Quantity])

 

However the issue is that I need to check this running sum within the "Allocation Column". The two work together. 

 

Psuedo code for Allocation would be ...

 

If running sum < max allowed

    then Monthly Quantity to Assign

    else 0

 

This methodology however gives me a circular dependancy error since the running sum is a derivation of the previous months "Allocation". 

v-janeyg-msft's avatar
v-janeyg-msft
Icon for Community Support rankCommunity Support
4 years ago

Hi, Anonymous 

 

Accordint to your description, I think you can create a column.

Like this:

Allocated Quantity =
VAR TOTAL =
    SUMX (
        FILTER ( Table, [Date] <= EARLIER ( [Date] ) ),
        [Monthly Quantity to Assign]
    )
RETURN
    IF ( TOTAL > [Max Quantity], 0, TOTAL )

 

Best Regards,
Community Support Team _ Janey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thank you for the response. This doesn't work as it will just double [Monthly Quantity to Assign] when there are multiple products. It will work with the example data, but as I said, there can be multiple products by account and date.

     

    The formula you provided is a running sum of a column up until a certain point. I need a calculation where the running sum of the calculation itself cannot exceed a certain point and I don't think power bi offers that level of recursion.  I've concluded that I just don't have the flexibility to do this within power bi and wrote a sql script to calculate it at source.

    • v-janeyg-msft's avatar
      v-janeyg-msft
      Icon for Community Support rankCommunity Support
      4 years ago

      Hi, Anonymous 

       

      Powerbi doesn't support recursion, but your needs are not completely recursive, it should be supported.

      How about this:

      Allocated Quantity =
VAR TOTAL =
    SUMX (
        FILTER (
            Table,
            [Date] <= EARLIER ( Table[Date] )
                && [Product] = EARLIER ( Table[Product] )
        ),
        [Monthly Quantity to Assign]
    )
VAR maxquantity =
    MAXX (
        FILTER ( Table, [Product] = EARLIER ( Table[Product] ) ),
        [Max Quantity]
    )
RETURN
    IF ( TOTAL > maxquantity, 0, TOTAL )

       

      Best Regards,
      Community Support Team _ Janey
      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.