Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
raya2015
New Member

How to update value calculation using last updated periods data

Hi All,

 

I would like the formula to look for the SKU per plant per month per year first in the table A, then the table B and if it cannot find it in either, it needs to look for the standard cost in the period that has the last update.

 

Eg. Taking SKU 85021 for plant abcd for the month of March 2020 (material year).

  • The std cost for month 3, year 2020 is not in Table A(image 1)
  • The std cost is not in Table B(image 2)
  • The formula would thus have to look at the months prior until it finds the std cost per unit, but it has to start from the previous month being February 2020.
  • For example if February 2020 did not have a std cost either, it needed to go and look at January 2020, and if January did not have a std costing either, it needs to look at December 2019 etc.

Image 1:

Material numberPlantStd costPosting periodMaterial posting year
000000000000085021abcd87.8422020
000000000000085021abcd92.0742020
000000000000085021abcd92.0752020
000000000000085021abcd92.07112020
000000000000085021abcd82.0792020
000000000000085021abcd92.0762020
000000000000085021abcd93.0782020
000000000000085021abcd92.0772020
000000000000085021abcd97.07102020
000000000000085021abcd92.0712020
000000000000085021abcd92.07122020

 

 

Image 2:

Material numberPlantStandard cost per unitStd cost
000000000000085021abcd95.5595.55

 

I have created in the 3rd table value column as below : 

 

$ Value = if(('SAP transition WH'[Quantity]*related('MBEWH Updated std cost'[Std cost per unit]))<>0,('SAP transition WH'[Quantity]*related('MBEWH Updated std cost'[Std cost per unit])),('SAP transition WH'[Quantity]*related('MBEW Costing mapping'[Standard cost per unit])))
 
but here where posting period is missing that value is coming as blank which I wanted to be reflected as previous month's value there.
 
raya2015_2-1632150665613.png

here value column is blank for march as period 3 is not there in the table 1 or 2. In this case it should calulate the value for period 2 as that is the previous update in period column for both tables. 

 

Please let me know if anyone of you have any solution for this.

 

Thanks in advance!

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

Hi @raya2015,

According to your description, it seems like you want to get the value of the last available date from another table based on the current date.

If that is the case, you can extract the current 'Material number' and date to looping other table records:

lastvalue =
VAR currNumber =
    SELECTEDVALUE ( Table[Material number] )
VAR currDate =
    MAX ( Table[Date] )
VAR _lastdate =
    CALCULATE (
        MAX ( Table2[Date] ),
        FILTER (
            ALLSELECTED ( Table2 ),
            [Material number] = currNumber
                && [Date] <= currDate
        )
    )
RETURN
    CALCULATE (
        MAX ( Table2[Value] ),
        FILTER (
            ALLSELECTED ( Table2 ),
            [Material number] = currNumber
                && [Date] = _lastdate
        )
    )

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

1 REPLY 1
v-shex-msft
Community Support
Community Support

Hi @raya2015,

According to your description, it seems like you want to get the value of the last available date from another table based on the current date.

If that is the case, you can extract the current 'Material number' and date to looping other table records:

lastvalue =
VAR currNumber =
    SELECTEDVALUE ( Table[Material number] )
VAR currDate =
    MAX ( Table[Date] )
VAR _lastdate =
    CALCULATE (
        MAX ( Table2[Date] ),
        FILTER (
            ALLSELECTED ( Table2 ),
            [Material number] = currNumber
                && [Date] <= currDate
        )
    )
RETURN
    CALCULATE (
        MAX ( Table2[Value] ),
        FILTER (
            ALLSELECTED ( Table2 ),
            [Material number] = currNumber
                && [Date] = _lastdate
        )
    )

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.