CODEWITHSUNDEEP
powerbipowerbi-desktop
Snowy
Snowy

Reputation: 6122

PowerBI and MAX for Multiplying Values?

log1    
date    cost
15-May  10
18-May  15
17-May  12
    
    
log2    
date    cost
15-May  1
16-May  3
18-May  2

What I want to do is create a "curent cost" Measure by finding the latest cost value based on the date in log1 (18-May and $15) and multiply it by the latest cost in log2 (18-May and 2). I then want to put that measure in a simple bar chart with the date, in this example that is 18-May and $30.

I played around with MAX but can't seem to figure this out. Help?

Thanks!

I think this is similar to Multiply columns based on the Year and Month but not sure.

Upvotes: 0

Views: 869

Answers (3)

jezza_bro
jezza_bro

Reputation: 51

If you're tables really are that simple, you could use the dax function called lookupvalue Its similar to a vlookup in excel and you can look up the cost based on the date

So in this example it would be added to the log2 table

Amount = 'log2'[cost] * LOOKUPVALUE('log1'[cost],'log1'[date],'log2'[date])

https://learn.microsoft.com/en-us/dax/lookupvalue-function-dax

If there was a relationship between the columns on date you'd able able to use the Related() function

Upvotes: 1

Angelo Canepa
Angelo Canepa

Reputation: 1781

Here is an alternative solution, assuming the following:

  • There is a unique cost per day in each log table
  • No empty missing dates, although it doesn't break the calculation.
  • The Calendar Table stores the dates.

Data Model

enter image description here

Calendar Table: Calculation

Calendar =
CALENDAR (
    MIN ( MIN ( log1[date] ), MIN ( log2[date] ) ),
    MAX ( MAX ( log1[date] ), MAX ( log2[date] ) )
)

Calculation: Measure

This calculation is dependant of using Calendar[Date] as the central Date value in your visuals.

Cost = sum(log1[cost])*sum(log2[cost])

Output 1

enter image description here

Alternatively, if you want to create a calculation that's independent of having date as part of the visual and retrieve the right totals.

CostSumx = 
sumx('Calendar',RELATED('log1'[cost])*RELATED(log2[cost]))

This means you need to change your relationships to 1:1 and BOTH

enter image description here

Output 2

enter image description here

Tables

log1

date cost
15 May 2021 20
16 May 2021 30
17 May 2021 40

log2

date cost
15 May 2021 1
16 May 2021 2
17 May 2021 3

Upvotes: 0

msta42a
msta42a

Reputation: 3741

What kind of output do you need?

Measure = 
var maxLog1 = CALCULATE( MAX(Log1[date]), REMOVEFILTERS(Log1[date]))
var maxlog2 = CALCULATE( MAX(log2[date]), REMOVEFILTERS(Log2[date]))

var result = CALCULATE( MAX(Log1[cost]), Log1[date] = maxLog1) * CALCULATE(MAX(log2[cost]), log2[date] = maxlog2)
return
result

enter image description here

Upvotes: 3

Related Questions