Forum Discussion

strat's avatar
strat
New Member
7 years ago
Solved

Revenue Sum Card with OR filter from two tables

Hi,   Sorry if this is really basic. I'm new to Power BI and while I'm going through tutorials I am stuck with a real world problem that I've not learnt how to solve yet.   I have two tables   ...
Need Help
  • affan's avatar
    affan
    7 years ago

    Hi strat,

    You can create the below measure. Considering your table names are Table1 and Table2.

     

    TotalR = 
var curntmon=SUMX(FILTER(CALCULATETABLE(ADDCOLUMNS(Table1,"ForecastDate",LOOKUPVALUE(Table2[FORECAST],Table2[ORDER NUMBER],Table1[ORDER NUMBER]))),MONTH([ForecastDate])=MONTH(TODAY())),CALCULATE(VALUES(Table1[REVENUE])))
var installed= SUMX(FILTER(CALCULATETABLE(ADDCOLUMNS(Table1,"ForecastDate",LOOKUPVALUE(Table2[FORECAST],Table2[ORDER NUMBER],Table1[ORDER NUMBER]))),MONTH([ForecastDate])<>MONTH(TODAY())&& Table1[STAGE]="Installed"),CALCULATE(VALUES(Table1[REVENUE])))
return 
curntmon+installed

    I have got the following result.

     

     

     

    You can also download the pbix file here.

    If this helped you, please mark this post as an accepted solution and like to give KUDOS .

     

    Regards,

    Affan

affan's avatar
affan
Icon for Solution Sage rankSolution Sage
7 years ago

Hi strat,

You can create the below measure. Considering your table names are Table1 and Table2.

 

TotalR = 
var curntmon=SUMX(FILTER(CALCULATETABLE(ADDCOLUMNS(Table1,"ForecastDate",LOOKUPVALUE(Table2[FORECAST],Table2[ORDER NUMBER],Table1[ORDER NUMBER]))),MONTH([ForecastDate])=MONTH(TODAY())),CALCULATE(VALUES(Table1[REVENUE])))
var installed= SUMX(FILTER(CALCULATETABLE(ADDCOLUMNS(Table1,"ForecastDate",LOOKUPVALUE(Table2[FORECAST],Table2[ORDER NUMBER],Table1[ORDER NUMBER]))),MONTH([ForecastDate])<>MONTH(TODAY())&& Table1[STAGE]="Installed"),CALCULATE(VALUES(Table1[REVENUE])))
return 
curntmon+installed

I have got the following result.

 

 

 

You can also download the pbix file here.

If this helped you, please mark this post as an accepted solution and like to give KUDOS .

 

Regards,

Affan

  • strat's avatar
    strat
    New Member
    7 years ago

    Hi affan,

     

    Thanks so much for the quick response. I've tested it with my data and it worked perfectly.  And I can see what you've done and it makes perfect sense.

     

    I really appreciate the help.

    Thanks,