Reply
Member
Posts: 52
Registered: ‎07-05-2018
Accepted Solution

Summarized table with today date alone

[ Edited ]

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.


Accepted Solutions
Highlighted
Community Support Team
Posts: 7,266
Registered: ‎05-02-2017

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.

View solution in original post

Attachment

All Replies
Highlighted
Community Support Team
Posts: 7,266
Registered: ‎05-02-2017

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.
Attachment