Forum Discussion

joe_2802's avatar
joe_2802
New Member
8 years ago
Solved

Value comparison between dates based on filters

Trying to achieve the following and would very much appreciate any help:

 

Report 1 (Date Filter):

DateCategoryCarrots
02.03.2017Donald2.630.266.580
02.03.2017Bugs Bunny113.204.133
02.03.2017Micky190.531.016
02.03.2017Goofy186.729.246
02.03.2017Mini53.912.554
02.03.2017Ricky48.941.252

 

Report 2 (Date Filter):

03.03.2017Donald2.631.032.71481,62
03.03.2017Bugs Bunny115.348.2123,58
03.03.2017Micky186.748.7485,79
03.03.2017Goofy186.875.5535,80
03.03.2017Mini54.630.1541,69
03.03.2017Ricky48.949.397

1,52

 

Report 3:

I would need to provide this the changes from Report 1 to Report 2 dynamically, based on the respective filters of report 1 and report 2. All raw data is one table. For example:

 

CategoryChange in CarrotsChange in Percentage
Donald766.1340,02
Bugs Bunny2.144.0800,07
Micky-3.782.267-0,12
Goofy146.3080,00
Mini717.6000,02
Ricky8.1460,00

 

Thank you!

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    joe_2802 assuming data is in 2 tables and hooked to the date dimension. if it isnt go to power query and duplicate date as a step

     

    change = 

    var dat1value = calculate(sum(value), filter(table1, table1[date] = [filter1date])

    var dat2value = calculate(sum(value), filter(table2, table2[date] = [filter2date])

     

    return dat1value - dat2value

     

    • joe_2802's avatar
      joe_2802
      New Member
      8 years ago

      Thanks! Have to walk, before I run, apologies in advance:

      Data is one table.

       

      I think I am almost able to put the path to solving it together, but I am not sure what to do specifically in terms of the solution.