Need to calculate this complex function.

[shubh25]

I am trying to do a complex formulae using measures.

A = Cumulative Amount HC (starting from Year) 
B = Current Month exchange Factor
C = Cumulative Amount HC (starting from Year upto Last Month)
D = Previous Month exchange Factor

Required EUR Formulae = ( A * B ) - ( C * D )

Linking the file with pbix and sample dataset.

dropbox

Adding screenshot of the Dashboard:-

[v-diye-msft]

Hi @shubh25 

A = Cumulative Amount HC (starting from Year) 

Use this measure:

Amount (HC) = TOTALYTD(SUM(DataSource[Amount (HomeCurrency)]),DatesTable[Date])

B = Current Month exchange Factor (What's the computational logic?)

C = Cumulative Amount HC (starting from Year upto Last Month) (Don't understand, plz show some examples)
D = Previous Month exchange Factor(What's the computational logic?)

[shubh25]

Hi @v-diye-msft,

These computations were a little complex so I just added a PBI file in the dropbox attachment.

basically, 
B = Current Month exchange Factor (current month exchange factor is being taken from another table using lookupvalue)
C = For the month of July, calculate YTD upto June
D = same as B but for prevous month

Please refer to the PBIX, if you still have doubts after seeing the PBIX, let me know. Thanks.

[v-diye-msft · Accepted solution]

Hi @shubh25 

Please kindly check below measures whether helps:

A:

Amount (HC) = TOTALYTD(SUM(DataSource[Amount (HomeCurrency)]),DatesTable[Date])

B:

Please created 1 calculated column in Datasource table, then manage the relationship with Exchange table:

LastoftheMonth = 
    DATE(
        YEAR(DataSource[Txn Date]),
        MONTH(DataSource[Txn Date])+1,
        1)-1

Note the directions of filter:

Then we get the factors:

C:

Cumulative Amount HC = TOTALYTD(SUM(DataSource[Amount (HomeCurrency)]),DatesTable[Date],ALL(DatesTable[Date]),"6/30")

 😧

Measure = var a  = MAX('Exchange Rates'[Date])
var b = DATE(YEAR(a),MONTH(a),1)-1
Return
CALCULATE(MAX('Exchange Rates'[Factor]),FILTER(ALL('Exchange Rates'),[Date]=b))

pbix attached,