Summarized table with today date alone

[indhu]

Hi all, 

I need help in getting a table/ measure with today date alone. 

Sample data:

Table 1:

Date            | Gold company name  | Price 

-----------------------------------------

1/10/2018      x                               100

12/10/2018    y                               200

25/10/2018    x                               400

25/10/2018    y                               500

Expected output:

Date            |   GCN      | Price 

-----------------------------------------

25/10/2018    x                 400

25/10/2018    y                 500

After finding this I want to multiply that with a column in another table to find out the current trend

Table 2:

Username |Gold company name | Balance

----------------------------

Ram                x                             10

Issac               y                             20

Seetha             x                            30

Expected output:

Username |Gold company name | Balance

----------------------------

Ram                x                          4000 (10*400)

Issac               y                          1000  (20*500)

Seetha             x                        12000 (30*400)

I can do this by creating a query in sql,

1. create a table with current date data alone

2. Importing that table into power bi

3. Create a relationship using city among table 1 and table 2

4. Create a summarised table to get the product

Current_rate_summary = SUMX(SUMMARIZE(Table1,Table1[price],table2[balance],"product",Table1[price] * table2[balance]),[product])

Can somebody please say if there is a better way to do this? I thought it will be better to create a summarised table to get the current date only but cannot a figure out a way to get that.

Thanks,

Indhu.

[v-jiascu-msft · Accepted solution]

Hi Indhu,

The first one could be achieved by the Visual Level Filter. The second one could be solved by a measure. Please check out the demo in the attachment.

Measure =
LOOKUPVALUE (
    Table1[Price],
    Table1[Date], TODAY (),
    Table1[Gold], MIN ( 'Table2'[Gold] )
)
    * MIN ( Table2[Balance] )

Best Regards,
Dale