Previous Year YTD Sales not showing properly using USERELATIONSHIP and COALESCE functions

[beepboop]

I have a page for my report that includes a year slicer (with a dropdown for Current Year, 2022, 2021, etc..). The slicer is based on a date table that ranges from 2020 up to year 2050. The table is unique in which it only contains the first day of every month (January 1, 2020, Feb 1, 2020, etc...)

I am trying to calculate the sales of the prior year YTD. The measure I am using is incorrect as it returns $0. If the year slicer is set to 2022, the measure below should return the YTD sales of 2021, from Jan 2021 to September 6, 2021 (at the time of this writing). The year slicer defaults to the current year so the measure below should display YTD sales for 2022 (Jan 2022 to Sept 6, 2022).

I am using the USERELATIONSHIP function in the measure so that the sales date corresponds to the year slicer when I filter the page.

However, the measure that I am using returns $0 in sales. Any idea on how to correct this?

Prior Year YTD Sales =

VAR CurrentDate = TODAY()

VAR PreviousYearStartDate = DATE(YEAR(CurrentDate) - 1, 1, 1)

VAR CurrentYearStartDate = DATE(YEAR(CurrentDate), 1, 1)

RETURN COALESCE( CALCULATE( SUM(sales_table[total_sales]), FILTER( sales_table, sales_table[customer] <> "testcustomer" && sales_table[category] <> "testcategory" && sales_table[sales_date] >= PreviousYearStartDate && sales_table[sales_date] <= CurrentYearStartDate - 1 ), USERELATIONSHIP(sales_table[sales_date], 'date'[date]) ), 0 )

[isjoycewang]

Hi @beepboop ,

Please try below measure and see if it works for you.

*replace the 'Table' to your TableName

LastYear YTD = 
var maxdate= MONTH(TODAY())
var prioryear = SELECTEDVALUE('Table'[Year]) - 1
var result =
CALCULATE( 
    SUM('Table'[Value]), 
    FILTER(
        ALL('Table'), 
        'Table'[Date].[MonthNo] <= maxdate && YEAR('Table'[Date]) = prioryear))
return result

[beepboop]

Sorry it is not fixed. Because i removed the userelationship function, if i filter my year slicer to the previous year, the measure does not update. The measure is stuck to return the ytd sales for 2022. If i select the year slicer to be 2022, the measure should update to reflect ytd sales for 2021 and vice versa

[beepboop]

I even tried this and it returns $0 for sales

Last YTD Total Sales =

VAR CurrentDate = TODAY()

VAR PreviousYearStartDate = DATE(YEAR(CurrentDate) - 1, 1, 1)

VAR CurrentYearStartDate = DATE(YEAR(CurrentDate), 1, 1)

VAR CurrentMonth = MONTH(CurrentDate)

VAR EndDate = DATE(YEAR(CurrentDate) - 1, CurrentMonth, DAY(CurrentDate))

RETURN

    COALESCE(CALCULATE(SUM(sales_table[total_sales]),FILTER(sales_table, sales_table[customer] <> "test customer" && sales_table[category] <> "test category" && sales_table[sales_date] >= PreviousYearStartDate && sales_table[sales_date] <= EndDate), USERELATIONSHIP(sales_table[sales_date], 'date'[date])),0)

[beepboop]

Hello, how do I use my fact table and date table in the formula? Also, please see my comment here

https://www.reddit.com/r/PowerBI/comments/16bwlvw/comment/jzhi2a3/?utm_source=share&utm_medium=web2x... 

[beepboop]

Nevermind i solved it. I removed the userelationship function in my measure