Power BI Previous Month Loop Measure

[lewisc_97]

Hi All,

I am trying to replicate the below table in Power BI. In Column C - F is actuals for the that month I need to calculate columns G, K & O. These Columns are calculated from the previous month, Ie. Column G = C + D - E - F, then Column K = G + H - I - J and so fourth and so fourth.  I have tried using Previous Month function but that doesn't work. 

I imagine it will have to be a loop of some sort? Any help is appreicated thanks

[v-rongtiep-msft]

Hi @lewisc_97 ,

Does that make sense? If so, kindly mark my answer as the solution to close the case please. Thanks in advance.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lewisc_97]

Hi  

This does make sense but I don't think it has the outcome I desire. 

The issue arrives in July WIP 2022. I need this number to be;

July WIP 2022 = June WIP 2022 + June Sales 2022 - June PIC 2022 - June Installs 2022 

74 = 58 + 30 - 2 -12 and it is currently 19, as the formula doesn't recognise the month prior 

I need this repeating for every month after so

August WIP 2022 = July WIP 2022 + July Sales 2022 - July PIC 2022 - July Installs 2022 etc etc.

[v-rongtiep-msft]

Hi @lewisc_97 ,

If I have misunderstood your meaning, please provide a pbix file without privacy information and desired output.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lewisc_97]

Hi @v-rongtiep-msft, 

I can't seem to attach the report. The link to it is if that helps https://app.powerbi.com/reportEmbed?reportId=a2720902-8bf3-4aab-a206-dd3b30ca8cc6&autoAuth=true&ctid...

Top matrix is what I can only produce the bottom is my desired output. If you can see WIP Addressable for months >May 2022 are claculated by the following 

New Month Wip Adressible - Prior Month WIP + Prior Month Sales - Prior Month PIC - Prior Month Installed. 

The code I have attempted to solve this is the below

Volume WIP Addressable =

IF (

'Summary Table'[Months] > TODAY (),

CALCULATE (

SUM ( tbl_Addressable_WIP[Volume_WIP_Addressable] ),

FILTER (

tbl_Addressable_WIP,

tbl_Addressable_WIP[SnapshotDate_WIP_Addressable] = 'Summary Table'[Previous Month]

),

FILTER (

tbl_Addressable_WIP,

tbl_Addressable_WIP[Product_WIP_Addressable] = 'Summary Table'[Product]

)

)

+

CALCULATE (

SUM ( Budget[Value] ),

FILTER ( Budget, Budget[Measure] = "Sales" ),

FILTER ( Budget, Budget[Unit] = "Volume" ),

FILTER ( Budget, 'Summary Table'[Previous Month] = Budget[Month] ),

FILTER(Budget,Budget[Version] = DATE(2022,03,01)),

FILTER ( Budget, Budget[Product] = 'Summary Table'[Product] )

)

-

CALCULATE (

SUM ( Budget[Value] ),

FILTER ( Budget, Budget[Measure] = "Installed" ),

FILTER ( Budget, Budget[Unit] = "Volume" ),

FILTER ( Budget, 'Summary Table'[Previous Month] = Budget[Month] ),

FILTER(Budget,Budget[Version] = DATE(2022,03,01)),

FILTER ( Budget, Budget[Product] = 'Summary Table'[Product] )

)

,

CALCULATE (

SUM ( tbl_Addressable_WIP[Volume_WIP_Addressable] ),

FILTER (

tbl_Addressable_WIP,

tbl_Addressable_WIP[SnapshotDate_WIP_Addressable] = 'Summary Table'[Months]

),

FILTER (

tbl_Addressable_WIP,

tbl_Addressable_WIP[Product_WIP_Addressable] = 'Summary Table'[Product]

)

)

)

However, in the top table you can see in July 2022, it stops taking the previous month Adressable WIP value. Hopefully this makes sense, if there is another way to send the hard copy over of the report let me know 

Thanks,
Lewis 

[v-rongtiep-msft]

Hi @lewisc_97 ,

I cannot get it due to privacy regulations. You can provide links by onedrive (or Google).

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lewisc_97]

Hi @v-rongtiep-msft 

This link should work to the Google Drive 

https://drive.google.com/file/d/1RBk1E6R9ykG08AWMNwTThU8QXXJ_w5mK/view?usp=sharing

[v-rongtiep-msft]

Hi @lewisc_97 ,

Please refer to my pbix file to see if it helps you.

Create a column.

_sum_all =
VAR _result = 'Table'[WIP Addressable] + 'Table'[Ssles] - 'Table'[PIC] - 'Table'[Inatalls]
VAR _previous =
    CALCULATE (
        SUM ( 'Table'[WIP Addressable] ),
        FILTER ( 'Table', 'Table'[date] = 'Table'[previous] )
    )
RETURN
    IF ( ISBLANK ( _previous ), 'Table'[WIP Addressable], _result )

If I have misunderstood your meaning, please provide a pbix file without privacy information and desired output.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.