cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
indhu Member
Member

Summarized table with today date alone

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support Team
Community Support Team

Re: Summarized table with today date alone

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] )

Summarized-table-with-today-date-alone

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 REPLY 1
Highlighted
Community Support Team
Community Support Team

Re: Summarized table with today date alone

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] )

Summarized-table-with-today-date-alone

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.