Calculate Value Change / Value Difference between given Dates

[nexaframe]

Hello Community,

as I could not find any answer here or on other platforms, I would like to get your support with my first contribution:

I have a dataset of stock reports looking basically like this:  UpdateDate, Type and Value.

For each ReportDate only Types with a Value are included. That means, if a Type is reduced to 0 between two reports, it will not be indicated, they just disappear. New Types can also show in newer reports without previous data.

ReportDate	Type	Value
5/10/2022	123	10
5/16/2022	123	10
5/23/2022	123	10
5/10/2022	345	20
5/10/2022	456	50
5/16/2022	456	50
5/23/2022	456	20
5/23/2022	567	15
5/16/2022	789	25
5/23/2022	789	30

I want to see the changes of  the values. An example for the output would be (grouping by Type, for better understanding only)

Type/CalendarDate	5/10/2022	5/16/2022	5/23/2022
123	+10	0	0
345	+20	-20	0
456	+50	0	-30
567	0	0	+15
789	0	+25	+5

As the reports are not provided in a regular way, I created in the first step a CalendarTable and linked it to my dataset.

But then I am struggling with the start/end date to filter my calculation.

I guess I need variables indicating the last value, last date of the value and somehow an indicator that it is treated as zero, if there is no value for the type at a selected date.

Hope you have some suggestions.

Thank you!

[Anonymous]

Hi @nexaframe ,

It works well. Could you please provide your pbix file without privacy information for me to study?

"The "total" calculation is not working."

Please refer to it.

Dealing with Measure Totals 

Or create a measure base on measure_2.

Measure_3 =
VAR _a = [Measure_2]
VAR _b =
    SUMMARIZE ( 'Table', 'Table'[Type], "aaa", [Measure_2] )
RETURN
    IF ( ISINSCOPE ( 'Table'[Type] ), _a, SUMX ( _b, [aaa] ) )

Best Regards

Community Support Team _ Polly

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

[nexaframe]

Hello and thank you again,

as I can't find a solution to upload it here, I can share it via this link: here 

[Anonymous]

Hi @nexaframe ,

We have create a table2 before.

Then put the table 2 [reportdate] into the column filed in the matrix!

Best Regards

Community Support Team _ Polly

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

[Anonymous · Accepted solution]

Hi @nexaframe ,

Please refer to my pbix file to see if it helps you.

Create  a table.

Table 2 = VALUES('Table'[ReportDate])

Then put the table 2 [reportdate] into the column filed.

Create two measures.

Measure =
VAR _oreplrblank =
    CALCULATE (
        MAX ( 'Table'[Value] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Type]
                IN VALUES ( 'Table'[Type] )
                    && 'Table'[ReportDate] IN VALUES ( 'Table 2'[ReportDate] )
        )
    )
VAR _harereplace =
    IF ( _oreplrblank = BLANK (), 0, _oreplrblank )
RETURN
    _harereplace

Measure_2 =
VAR _DATEBEFORE =
    CALCULATE (
        MAX ( 'Table 2'[ReportDate] ),
        FILTER (
            ALL ( 'Table 2' ),
            'Table 2'[ReportDate] < MAX ( 'Table 2'[ReportDate] )
        )
    )
VAR _VALUEBEFORE =
    CALCULATE (
        [Measure],
        FILTER ( ALL ( 'Table 2' ), 'Table 2'[ReportDate] = _DATEBEFORE )
    )
RETURN
    [Measure] - _VALUEBEFORE

Best Regards

Community Support Team _ Polly

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

[nexaframe]

Hello @Anonymous 

Thanks for your support.

Somehow it works in your attached file, but when I copy the exact same solution to the test data, it doesn't?

Unfortunately I can not upload the file here?

Also seeing your solution: The "total" calculation is not working by type and also not by date?

Any help is really appreciated.

Thank you!

[amitchandak]

@nexaframe , Try a new column like

Diff with last date =
var _1 = maxx(filter(Table, Table[Type] = earlier(Table[Type]) && [ReportDate] < earlier([ReportDate])),[ReportDate])
return
[Value] - maxx(filter(Table, Table[Type] = earlier(Table[Type]) && [ReportDate] < earlier([ReportDate])),[Value])

[nexaframe]

@amitchandakthanks for your super fast reply. Unfortunately it works only partially.

- It works if there is data for a type on all reportdates and it works if a new type is popping up on a later report date and not from the beginning. BUT: It doesn't work if a type just disappears.

In my example above, the issue is with Type "345", it shows "20 on the first date, but than empty columns for the rest. Expected would be "-20" on the next date, then later 0.

Do you have any suggestions?

Thank you!