Convert YTD volumes (accumulative) to monthly volumes

[Sergi]

Hi all,

Hope some could find a solution.  I have a date sheet where the volumes by month shows only the accumulative volumes considering the months before.  and I would like to add a column that showed me the absulute monthly volumes.

The fields in my case are  date (year.month),  sales (€), customers

I've just looked in another forums, but with no success with the formulas.

ex. 

Date       Sales

2023.1    5

2023.2    8

2023.3    10

and i would like to see

Date       Sales

2023.1    5

2023.2    3

2023.3    2

[Ahmedx]

pls try rhis

Column = 
VAR _CurrentDate ='Table'[Date]
RETURN
'Table'[Sales]-
CALCULATE(
    max( 'Table'[Sales] ),ALL('Table'),
  
    'Table'[Date]<_CurrentDate)

[Sergi]

Hi, 

Just forgot to mention, that have several values for each Date due to customer fields. 

I 'm having the error, that cannot be a unique value for the column Date.

[Ahmedx]

show an example and the result you expect

[Sergi]

Should be like this:

[v-xinruzhu-msft]

Hi @Sergi 

You can create a measure

Measure =
VAR a =
    MINX ( ALLSELECTED ( 'Table' ), [Date] )
RETURN
    IF (
        SELECTEDVALUE ( 'Table'[Date] ) = a,
        CALCULATE ( SUM ( 'Table'[Sales] ) ),
        SUM ( 'Table'[Sales] )
            - CALCULATE (
                SUM ( 'Table'[Sales] ),
                OFFSET ( -1, ALLSELECTED ( 'Table'[Date] ), ORDERBY ( [Date], ASC ) )
            )
    )

Output

Best Regards!

Yolo Zhu

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

[Ahmedx]

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Convert YTD volumes.pbix

[Sergi]

Hi, could you write the formula, please. Cannot download the file due to enterprise privileges.

Regards,

Sergi

[Ahmedx]

_Sales = sum('Table'[Sales]) 
----------
Final = IF(
    ISFILTERED( 'Table'[Customer] ), [_Sales]-
    CALCULATE(
        [_Sales],
        OFFSET(
            -1,
            SUMMARIZE(
                ALLSELECTED( 'Table' ),
                'Table'[Customer],
                'Table'[Date]
            ),
            ORDERBY( 'Table'[Date] ),DEFAULT,PARTITIONBY('Table'[Customer])
        )
    ), [_Sales]-
    CALCULATE(
        [_Sales],
        OFFSET(
            -1,
            SUMMARIZE( ALLSELECTED( 'Table' ), 'Table'[Date] ),
            ORDERBY( 'Table'[Date] )
        )
    )
)