Difference of two columns (distinction stored in rows)

[kgr]

Hello, 

I have a report showing articles and a inventory value per month. The month information is stored in one column and contains e.g. 01-2019, 02-2019. 

Article 1  /  01-2019: 100   02-2019: 200

I would like to calculate Table[inventory value] as difference for the last period and the previous one = 200-100 = +100.

[v-juanli-msft · Accepted solution]

Hi

Create a date table and create a relationship between this date table and your table.

CALENDAR =
ADDCOLUMNS (
    CALENDARAUTO (),
    "MM-YY", FORMAT ( [Date], "mm-yyyy" ),
    "year", YEAR ( [Date] ),
    "month", MONTH ( [Date] )
)

Create measures in your table, 

Measure =
VAR noblank =
    CALCULATE (
        SUM ( 'Table'[value] ),
        FILTER ( 'CALENDAR', 'CALENDAR'[Date] <> BLANK () )
    )
RETURN
    IF (
        SUM ( 'Table'[value] ) = BLANK (),
        CALCULATE ( 0, FILTER ( 'CALENDAR', 'CALENDAR'[Date] = BLANK () ) ),
        noblank
    )

Measure 3 = [Measure]-[Measure 2]

Add [article] and [Measure 3] in a table or matrix, you will get the result as expected.

Best Regards

Maggie

[kgr]

Hi Maggie, 

thank you for your help. I got the first part working. 

In the measure I get the following error, which I am clueless how to resolve: "The syntax for 'FILTER' is incorrect."

I replace TABLE with my tables name.

Thanks again for your help.

[v-juanli-msft]

Hi @kgr 

Make sure you create a measure, also you create relationships for the calendar table and your data table.

If you are unable to slove this, please share the screenshot which can make me clear more about this.

Best Regards
Maggie

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

[kgr]

Here comes the screenshot. Thanks again for your help.

[Mariusz]

Hi @kgr 

Can You provide a data sample?

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[kgr]