Forum Discussion

shubh25's avatar
shubh25
Helper I
6 years ago
Solved

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...
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-diye-msft's avatar
    v-diye-msft
    6 years ago

    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,

shubh25's avatar
shubh25
Helper I
6 years ago

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's avatar
v-diye-msft
Community Support
6 years ago

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,