Date Table Not Working

[scarlet61980]

Hello,  I started using PowerBI desktop a couple of months ago. It is set up for direct query and I have an appended query with a date which is single direction related to a Date Table.  It will not properly calculate prior YTD and current YTD sales totals using the DAX langauge as below:

YTD Sales = TOTALYTD(

        SUM('2022-2023 Health System Sales'[HS Sales Value]),'Date Table'[End of Month])

Prior YTD Sales = CALCULATE([YTD Sales],SAMEPERIODLASTYEAR('Date Table'[End of Month]))

I have already checked that the edit query language shows the date field as date and it shows as date type as well.

Is this b/c I'm using Direct Query? I have not done much with calculate/DAX in general other than creating measures up to this point but I feel like this shoudl be fairly simple.

[scarlet61980]

It is a date column in a date table I created that runs for several years starting with 1/1/2022. 

I have a table with sales from 1/1/2022-8/31/2023, I update it monthly.  I am trying to do a matrix visual that shows various time intelligent totals such as rolling 12 months sales, prior YTD and a variance of prior YTD vs Current YTD ultimately.  I have started with Prior YTD and it gives me the below in the visual which is the entire prior year (2022), expecting 1/1/2022-8/31/2022 as that is the latest date in the HS Sales Date column of the 2022-2023 Health Systems table I had in my original post (which is the column I have related to the Day in the Date Table).  

YTD Sales Formula as I originally did it:

Prior YTD:

[parry2k]

@scarlet61980 day is a date column? Weird name though if that is the case. Based on the expression it should work. When you say not working, what does it mean? Wrong value, error? How do you display the measure in the visual?

It is not easy to answer the question based on vague reply, if you provide more details it will be helpful. Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[scarlet61980]

I had tried the "Day" also intially but that did not work either.

[parry2k]

@scarlet61980 instead of using End of Month column it should be date column from the date dimension. I have full playlist on time intelligence you can check it here. 

Time Intelligence Playlist