Forum Discussion

flownfluid's avatar
flownfluid
Frequent Visitor
5 years ago
Solved

Showing difference between two rows with date selection

Hi all,

 

I have a table like this:

MaterialnumberDateValue
10001.01.2021100
10002.01.2021100
10003.01.2021200


The goal is that the user can select 2 dates and PowerBi show up the difference between the entries per Materialnumber.

 

In example, the user choose Date1 "01.01.2021" and Date2 "03.01.2021" and the result should be:

Materialnumber
100

 

How can I realize this issue, any ideas?

 

Thanks for your help

Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    flownfluid , assuming dates are selected in a single  slicer

     

    measure =
    Var _Min = MINX(allselected('Date'), 'Date'[Date]) //date table assumed
    Var _Max = MAXX(allselected('Date'), 'Date'[Date])
    return CALCULATE(SUM(Table1[Value]), filter(Table1, Table1[date]=_max)) -CALCULATE(SUM(Table1[Value]), filter(Table1, Table1[date]=_min))

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    flownfluid , assuming dates are selected in a single  slicer

     

    measure =
    Var _Min = MINX(allselected('Date'), 'Date'[Date]) //date table assumed
    Var _Max = MAXX(allselected('Date'), 'Date'[Date])
    return CALCULATE(SUM(Table1[Value]), filter(Table1, Table1[date]=_max)) -CALCULATE(SUM(Table1[Value]), filter(Table1, Table1[date]=_min))

  • v-janeyg-msft's avatar
    v-janeyg-msft
    Community Support
    5 years ago

    Hi, flownfluid 

     

    If I understand your needs, you can do this:

    1. Create a single date list as slicer.

    Like this:

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

    2. Create a measure to show the difference.

    Like this:

    Measure = 
var a=MINX( DISTINCT('Table 2'[Date]),[Date])
var b=MAXX( DISTINCT('Table 2'[Date]),[Date])
var c=SUMX(FILTER(ALL('Table'),[Date]=a&&[Materialnumber]=SELECTEDVALUE('Table'[Materialnumber])),[Value])
var d=SUMX(FILTER(ALL('Table'),[Date]=b&&[Materialnumber]=SELECTEDVALUE('Table'[Materialnumber])),[Value])
return d-c

    Did I answer your question ? Please mark my reply as solution. Thank you very much.
    If not, please upload some insensitive data samples and expected output.


    Best Regards,

    Community Support Team _ Janey