Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:-
Solved! Go to 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,
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?)
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:
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,
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |