Calculate/Substract values based on snapshot data from the same column

[Anonymous]

Hi there community,

I am trying to create a report in which the user is able to compare the difference in the values between a from and two dates. This meant that I had to historize data on a daily basis. I managed to settle that part by having daily snapshots of the data. The next step would be to have substraction done between the values of the dates 'from' and 'to'.

My current data looks like this:

Date	Dateorder	Contract	Value
06-08-2021	3	P001	10
06-08-2021	3	P002	12
07-08-2021	2	P001	11
07-08-2021	2	P002	12
08-08-2021	1	P001	12
08-08-2021	1	P002	13

I would like to be able to see the differences of the 'Value' column. The difference needs to be calculated based on a 'from' and 'to' filtering (For Example 'From' filter value '06-08-2021' and 'To' value '07-08-2021').

I am stuck in how to ensure that I can compare the value columns based on filtering with a from and to dates. Does anyone know a way to solve this?

Thanks!

Bunyamin 

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[v-yalanwu-msft · Accepted solution]

Hi, @Anonymous ;

Please try it.

Difference = 
VAR _MAX =MAXX ( ALLSELECTED ( 'Table' ), [Date] )
VAR _MIN =MINX ( ALLSELECTED ( 'Table' ), [Date] )
VAR _summax =
    CALCULATE ( SUM ( [Value] ), FILTER ( ALLSELECTED ( 'Table' ), [Date] = _MAX ) )
VAR _summin=
    CALCULATE ( SUM ( [Value] ), FILTER ( ALLSELECTED ( 'Table' ), [Date] = _MIN ) )
VAR _sum=
    CALCULATE (SUM ( [Value] ),FILTER ( ALLSELECTED ( 'Table' ),[Contract] = MAX ( [Contract] )&& [Date] = _MAX))
VAR _total =
    IF (ISINSCOPE ( 'Table'[Contract] ),_sum- SUM ( [Value] ),_summax- SUM ( [Value] ))
RETURN IF ( HASONEVALUE ( 'Table'[Date] ), _total, _summax-_summin)

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yalanwu-msft · Accepted solution]

Hi, @Anonymous ;

Sorry for the late reply, if you want show 0 , it should be create a new table.

1.create a new table.

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

2.create two measures

value = 
var _reault=CALCULATE(SUM('Table'[Value]),FILTER(ALLSELECTED('Table'), [Contract] in VALUES('Table'[Contract])&& [Date] in VALUES('Table 2'[Date])))
return IF(_reault<>BLANK(),_reault,0)

Difference2 = 
VAR _MAX =MAXX ( ALLSELECTED ( 'Table 2' ), [Date] )
VAR _MIN =MINX ( ALLSELECTED ( 'Table 2' ), [Date] )
VAR _summax =
    CALCULATE ([value], FILTER ( ALLSELECTED ( 'Table 2' ), [Date] = _MAX ) )
VAR _summax1 =
    CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( ALLSELECTED ( 'Table' ), [Date] = _MAX ) )
VAR _summin=
    CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( ALLSELECTED ( 'Table' ), [Date] = _MIN ) )
RETURN IF ( HASONEVALUE ( 'Table 2'[Date] ), _summax- [value], _summax1-_summin)

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you very much! This solves my issue.

[v-yalanwu-msft · Accepted solution]

Hi, @Anonymous ;

Please try it.

Difference = 
VAR _MAX =MAXX ( ALLSELECTED ( 'Table' ), [Date] )
VAR _MIN =MINX ( ALLSELECTED ( 'Table' ), [Date] )
VAR _summax =
    CALCULATE ( SUM ( [Value] ), FILTER ( ALLSELECTED ( 'Table' ), [Date] = _MAX ) )
VAR _summin=
    CALCULATE ( SUM ( [Value] ), FILTER ( ALLSELECTED ( 'Table' ), [Date] = _MIN ) )
VAR _sum=
    CALCULATE (SUM ( [Value] ),FILTER ( ALLSELECTED ( 'Table' ),[Contract] = MAX ( [Contract] )&& [Date] = _MAX))
VAR _total =
    IF (ISINSCOPE ( 'Table'[Contract] ),_sum- SUM ( [Value] ),_summax- SUM ( [Value] ))
RETURN IF ( HASONEVALUE ( 'Table'[Date] ), _total, _summax-_summin)

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Yalan,

This helps me a lot thank you!

I only seem to stumble when a new contract is made in a later date.

Example table:

Date	Dateorder	Contract	Value
06-08-2021	3	P001	10
06-08-2021	3	P002	12
07-08-2021	2	P001	11
07-08-2021	2	P002	12
08-08-2021	1	P001	12
08-08-2021	1	P002	13
08-08-2021	1	P003	12

P003 wasn't there earlier so naturally it tries to compare with nothing:

A fix for this would be to have the value which is empty to be filled by 0 but I can't figure it out where to modify the measure to do this.

Do you have suggestions? Thanks in advance!

[Ashish_Mathur]

Hi,

Based on the data that you have shared, please show the expected result.

[Anonymous]

Hi Ashish,

Basically I want it to look like this:

It needs to show the differences of the contracts in row level for two snapshot dates 'From' and 'To'.

This way I can see in details where the differences in my dataset happens when comparing snapshot dates.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[v-yalanwu-msft]

Hi, @Anonymous ;

You could create a measure.

Difference = 
var _max=MAX([Date])
var _min=MIN([Date])
return CALCULATE(SUM([Value]),FILTER(ALLSELECTED('Table'),[Date]=_max))-CALCULATE(SUM([Value]),FILTER(ALLSELECTED('Table'),[Date]=_min))

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Wauw this is great! This is exactly what I want. Is there a way to show the difference inside the table?

[v-yalanwu-msft]

Hi, @Anonymous ;

Please try modify the measure like below:

Difference = 
var _MAX=MAXX(ALLSELECTED('Table'),[Date])
var _MIN=MINX(ALLSELECTED('Table'),[Date])
return 
CALCULATE(SUM([Value]),FILTER(ALLSELECTED('Table'),[Date]=_MAX))-CALCULATE(SUM([Value]),FILTER(ALLSELECTED('Table'),[Date]=_MIN))

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Yalan Wu,

Thank you so much.

This is very close to what I mean. Is is possible to this row level like this?:

I want to compare the differences of the values for each contract based on snapshot date from and to. So it is okay if it is just two snapshot dates to filter that are comparing each other. It does not need to be in between.

[amitchandak]

@Anonymous , Try a measure like

New column =
var _max = maxx(filter(Table, [Date] <earlier([Date]) && [Contract] = earlier([Contract])),[Value])
return
[Value] -maxx(filter(Table, [Date] = _max && [Contract] = earlier([Contract])),[Value])