Forum Discussion

Anonymous's avatar
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 dai...
General Comment
Need Help
Show and Tell
Tips and Tricks
  • Ashish_Mathur's avatar
    Ashish_Mathur
    5 years ago

    Hi,

    You may download my PBI file from here.

    Hope this helps.

  • v-yalanwu-msft's avatar
    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.

  • v-yalanwu-msft's avatar
    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.

v-yalanwu-msft's avatar
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.

Anonymous's avatar
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's avatar
    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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.