Forum Discussion

scarlet61980's avatar
scarlet61980
Regular Visitor
2 years ago

Date Table Not Working

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.

 

 

Need Help

4 Replies

  • parry2k's avatar
    parry2k
    Super User
    2 years ago

    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's avatar
    scarlet61980
    Regular Visitor
    2 years ago

    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: