I am trying to reproduce a PowerBI measure into SQL Server. I calculated on Power BI the YTD Revenue like so:

YTD Revenue = CALCULATE([Total Revenue], DATESYTD('Calendar'[date]))

The Calendar table is made of the PK date column called Date which shows a rolling calendar from 01/01/1997 to 31/12/1998. Then there are other columns such as start of Month, Day Name, Year and so on.

The other table is called Transaction_data and it is composed by the FK column Transaction_date which links the PK columns of the Calendar table, stock_date, product_id, customer_id, store_id, Quantity, and Revenue.

I should see $872,924 in YTD Revenue in September 1998.

Having read some about YTD on the web, I have unsuccessfully tried the following two queries:

SELECT
    SUM(CASE 
            WHEN DATEDIFF(YEAR, calendar.date, GETDATE()) = 0 
                THEN transaction_data.Revenue 
            ELSE 0 
        END) AS ytd
FROM
    transaction_data
INNER JOIN
    calendar ON Calendar.date = Transaction_Data.transaction_date


SELECT 
    calendar.date, transaction_data.revenue, 
    SUM(REVENUE) OVER(ORDER  BY calendar.date) AS result 
FROM
    Transaction_Data
INNER JOIN
    calendar ON Calendar.date = Transaction_Data.transaction_date

The output of the first query is 0, whereas the numbers of the second query do not match at all the desired output.