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: DateDateorderContractValue06-08-20213P0011006-08-20213P0021207-08-20212P0011107-08-20212P0021208-08-20211P0011208-08-20211P00213 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

Hi, You may download my PBI file from here. Hope this helps.

Forum Discussion

Anonymous

Not applicable

5 years ago

Solved

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

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
5 years ago
Hi,

You may download my PBI file from here.

Hope this helps.

v-yalanwu-msft

5 years ago

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.

Calculate Substract values based on snapshot.pbix39 KB

v-yalanwu-msft

5 years ago

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.

Calculate Substract values based on snapshot.pbix51 KB

12 Replies

v-yalanwu-msft
Community Support
5 years ago
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.
Calculate Substract values based on snapshot.pbix39 KB
- Anonymous
  Not applicable
  5 years ago
  Wauw this is great! This is exactly what I want. Is there a way to show the difference inside the table?
  - v-yalanwu-msft
    Community Support
    5 years ago
    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.
    Calculate Substract values based on snapshot.pbix40 KB
v-yalanwu-msft
Community Support
5 years ago
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.
Calculate Substract values based on snapshot.pbix39 KB
- Anonymous
  Not applicable
  5 years ago
  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!

v-yalanwu-msft

Community Support

5 years ago

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.

Calculate Substract values based on snapshot.pbix51 KB

Anonymous
Not applicable
5 years ago
Thank you very much! This solves my issue.

amitchandak
Super User
5 years ago
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])
Ashish_Mathur
Super User
5 years ago
Hi,

Based on the data that you have shared, please show the expected result.
- Anonymous
  Not applicable
  5 years ago
  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
    Super User
    5 years ago
    Hi,
    
    You may download my PBI file from here.
    
    Hope this helps.

Forum Discussion

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

12 Replies

Related content

Power BI embedded in PowerPoint – Live /Snapshot/Public Snapshot buttons not working (recent regr?)

Warehouse Snapshots: Dropping tables deletes the tables from Snapshots

Data Snapshots

Warehouse snapshot

Embedded Public snapshot refresh in PPT