Repeat a application from its starting point until the End of the year

[kastenkk]

Hello all,

This is the current structure issue

1. The model is based on 2 fact table

     1.1 Register of reason that a part price fluctuates (linked with reason itself and date of application)

     1.2 Register of volume of such part on each month

            

The expectation is to calculate the impact of price-diff (1.1) on each month

       - if a part diff is applied on Jan. it will be impact all the month's volume from Jan on~

       - By applying a filter for Jan, only Jan (impact * volume) must be viwed

       

I've tryed to apply Calculate (sum), filtered by EOmonth (starting on application date), however the result is full sum of

Jan_partdiff * all volume of such part, but applied uniquely in Jan; which is wrong

I appreciate your attention,

Klaus

[v-vpabbu]

Hi @kastenkk,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Vinay Pabbu

[kastenkk]

Hello Vinay,
I did not work yet, unfortunatelly.

I'm trying to create virtual tables to gathering all the data at once (for examplo, if Feb = blank, then repeate Jan, something like that) and integrate volume and diff table into one,,
No success yet

[v-vpabbu]

Hi @kastenkk,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,
Vinay Pabbu

[v-vpabbu]

Hi @kastenkk,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,
Vinay Pabbu

[pankajnamekar25]

Hello @kastenkk 

You can try this measure

Impact =

SUMX (

    Fact1,

    VAR _Part = Fact1[PartNumber]

    VAR _Date = Fact1[Date]  -- Application date

    VAR _Diff = Fact1[PartDiff]

    RETURN

        CALCULATE (

            SUM ( Fact2[Volume] ) * _Diff,

            Fact2[PartNumber] = _Part,

            Fact2[Date] >= _Date

        )

)

Thanks,
 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.

[kastenkk]

Dear Pankaj,

Thank you by your quick reply.

The result was partially correct:

Fact1

Fact2

Formula

Result

At [February x Rsn1] cell is missing a 100.000, which is the multiplication of [Jan_Rsn1] * [Feb_Volume]

The logic behind is 

   - Any impact applied on Jan, must permeate forward throughout the year

   - So a impact in Jan could be much worst than a impact in Dec (due to volume monthly), for example

Thank you very much,
Klaus

[v-vpabbu]

Hi @kastenkk, 

The issue is that the January price change isn't propagating to subsequent months. To resolve this, the price fluctuation from January should continue impacting the volume for February, March, and beyond. Each month after January should multiply the volume for that month by the January price difference. The formula needs to ensure the impact carries forward to all months after the application date, including February.

Regards,

Vinay Pabbu

[kastenkk]

Hello Vinay,

Your statment is perfect and precise.

Can you support translating that into DAX?

Tks

[v-vpabbu]

Hi @kastenkk,

Try this

Impact :=
SUMX (
    FILTER (
        Fact1,
        Fact1[Date] <= MAX('Date'[Date])
    ),
    VAR _Part = Fact1[PartNumber]
    VAR _Diff = Fact1[PartDiff]
    RETURN
        CALCULATE (
            SUM ( Fact2[Volume] ) * _Diff,
            Fact2[PartNumber] = _Part,
            Fact2[Date] = MAX('Date'[Date])
        )
)

Regards,

Vinay Pabbu 

[kastenkk]

unfortunatelly the same result keeps happening

[v-vpabbu · Accepted solution]

@kastenkk, Try this, it finds all diffs from Jan and Feb, and multiplies each one by Feb volume.

Impact :=
VAR _CurrentMonth = MAX('Date'[Date])
RETURN
SUMX (
    FILTER (
        Fact1,
        Fact1[Date] <= _CurrentMonth  -- All diffs applied up to current month
    ),
    VAR _Part = Fact1[PartNumber]
    VAR _Diff = Fact1[PartDiff]
    RETURN
        CALCULATE (
            SUM ( Fact2[Volume] ) * _Diff,
            Fact2[PartNumber] = _Part,
            Fact2[Date] = _CurrentMonth  -- Only volume of current month
        )
)

Regards,

Vinay Pabbu