Follow us: Connect on YouTube Connect on YouTube Connect on YouTube

Pages

Thursday, 16 June 2016

Query to Get GL Exchange Rates



SELECT typ.user_conversion_type "Conversion Type"
,      rat.from_currency        "From Currency"
,      rat.to_currency          "To Currency"
,      MIN(conversion_date)     "Earliest Date"
,      MAX(conversion_date)     "Latest Date"
FROM   gl_daily_conversion_types typ
,      gl_daily_rates            rat
WHERE  rat.conversion_type = typ.conversion_type
AND    rat.from_currency   < rat.to_currency -- Ensure pairs only shown once.
GROUP BY typ.user_conversion_type
,        rat.from_currency  
,        rat.to_currency
ORDER BY 1,2,3
Share this article :

2 comments:

  1. Hello There,


    You make learning and reading addictive. All eyes fixed on you. Thank you being such a good and trust worthy guide.

    I have a data table with 80M records that looks like any other typical Sales Fact with fields like DateID, ProductID, SupplierID,QuantitySold and I am trying to write a DAX calculation that gives me the QuantitySold in past 30/60/90 days.
    The catch is if the month is part done (example: April 15th), then this measure should show only until that day as the total at the month level instead of as of last day of the month.
    I have tried many ways and finally ended up on this as it was working perfectly
    QtySold_Running30days:=
    VAR MaxDate = LASTDATE(FactSales[SaleDt])

    RETURN
    CALCULATE([QuantitySold],
    FILTER(ALL('Date'),
    'Date'[Date] > DataMaxDate -30 &&
    'Date'[Date] < DataMaxDate))
    Having said that, the reports that have all these 3 measures along with Date / Product and Supplier Attributes is not returning data even with the bare minimum filters.

    Thank you very much and will look for more postings from you.


    Thank you,
    Reena

    ReplyDelete
  2. Your blog never ceases to amaze me, it is very well written and organized.–;*; usd to eur

    ReplyDelete