Calculate Incremental values from Cumulative column

[neil7722]

I have a table with date in yyyymmdd text format. They are always apart by a week (7 days). The other column had cumulative values for Demand. How do I create another column to calculate the incremental value (weekly demand) ?

[v-jiascu-msft]

Hi @neil7722,

Could you please mark the proper answers as solutions?

Best Regards,

Dale

[Vvelarde]

@neil7722

hi, In Edit Query add a Index Column

After That create a new calculated column:

Incremental =
VAR Index = Table1[index] - 1
RETURN
    Table1[CurModDem]
        - CALCULATE (
            SUM ( Table1[CurModDem] );
            FILTER ( Table1; Table1[index] = Index )
        )

Regards

Victor

[neil7722]

Any way I can do this without the index? I can use the calulate function by with dates. I am able to convert the yyyymmdd to date and substract 7 days from it, but I am not able to convert it back to text format to use it in filter expression

[v-jiascu-msft]

Hi @neil7722,

Why do you use it in Text mode? They are dates. Why not using them as dates? It seems Power BI will do an implicit conversion. Please refer to the following example.

Column =
VAR currentDate = [Date]
RETURN
    CALCULATE ( SUM ( [Value] ), FILTER ( Table1, Table1[Date] < currentDate ) )

Best Regards,

Dale