Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
shubh25
Helper I
Helper I

Need to calculate this complex function.

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:-
eur table.PNG

 

1 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:

007.PNG

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

009.PNG

pbix attached,

Community Support Team _ Dina Ye
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

3 REPLIES 3
v-diye-msft
Community Support
Community Support

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

 

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

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.

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:

007.PNG

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

009.PNG

pbix attached,

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.