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

 

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,

3 Replies

  • v-diye-msft's avatar
    v-diye-msft
    Community Support
    6 years ago

    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'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,