How to create a Trend vs last Value without Date as basis

[floxxy]

Hi there,

I am not sure how to solve this, any Idea would be highly appreciated.

In simple terms, my table looks like this:

And

Step	Revenue
Step1	112
Step2	201
Step3	213
Step4	158
Step1	124
Step4	123

And the result should look like that and I don't know how to get the Trend vs. Previous Number calculated. 

	Sum of Revenue	Trend vs. Previous Number
Step1	236
Step2	201	-15%
Step3	213	6%
Step4	281	32%

[vega]

Is the first table your dataset?

If that is the dataset, the way I would approach would be the following:

1. Create a new column stepNum that contains the number of the step

StepNum = 
VALUE(REPLACE(Table1[Step],1,4,""))

1. The measure you need will be the following:

Tread vs. Previous Number = 
VAR num = SELECTEDVALUE(Table1[StepNum])
VAR answer = 
DIVIDE(
    [Sum of Revenue],
    CALCULATE(
        [Sum of Revenue],
         FILTER(
             ALL('Table1'),
             Table1[StepNum] = num-1
         )
    ),
    BLANK()
)
RETURN
IF(ISBLANK(answer), BLANK(), answer-1)

[floxxy]

Thank you for the quick response. Maybe I was oversimplifying the case, will try to explain it better. Forget the "Step1" thing. I have two Tables.

"Calendar" with Date ( with active relationship to Date in the "Report_2017" Table)

"Report_2017" with lots of data, one column is "Spend EUR"

One colum in "Calender" is called "Week_ongoing" and this counts weeknumbers from 1 to XXX (not limited to 52 weeks so it goes over different years as well as continuos number)

Calendar

Date	Week_ongoing
01.01.2017	1
02.01.2017	1
03.01.2017	1
04.01.2017	1
05.01.2017	1
06.01.2017	1
07.01.2017	1
08.01.2017	2
09.01.2017	2
10.01.2017	2
…	…
31.12.2017	52
01.01.2018	53
…	…
09.01.2018	54

Report_2017

Date	Spend EUR
30.12.2017	110
31.12.2017	126
01.01.2018	201
09.01.2018	100
10.01.2018	113
16.01.2018	281

The result schould look like before:

Week_ongoing	Spend EUR	Trend vs. Previous Spend
56	236
57	201	-15%
58	213	6%
59	281	32%

I get the first two columns obviousely simply in a table, but I don't get the right way to calculate the "Trend vs. Previous Spend"

Thank you again for your support!

[Ashish_Mathur · Accepted solution]

Hi,

You may download my solution from here.

Hope this helps.

[floxxy]

Thank you! Could have found this solution by myself... Now that I see the solution it is obvous. Thank you!

[Ashish_Mathur]

You are welcome.