Forum Discussion

neil7722's avatar
neil7722
New Member
8 years ago

Calculate Incremental values from Cumulative column

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) ?

 

4 Replies

  • Vvelarde's avatar
    Vvelarde
    Community Champion
    8 years ago

    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's avatar
      neil7722
      New Member
      8 years ago

      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's avatar
        v-jiascu-msft
        Microsoft Employee
        8 years ago

        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 ) )

        Calculate_Incremental_values_from_Cumulative_column

         

        Best Regards,

        Dale