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 :

1 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