User Profile
Nathan
Frequent Visitor
Joined 9 years ago
User Widgets
Contributions
Re: Bug: Date Function Issue
v-haibl-msft, You are correct; It does not explictly say 0 is supported. The point I'm trying to expose is DAX Date supports negative numbers, but fails to allow you to go back a single day (which appears to be where 0 comes into play). DATE(2017, 1, -2) = December 29, 2016 DATE(2017, 1, -1) = December 30, 2016 DATE(2017, 1, 1) = January 1, 2017 Notice how I cannot reach December 31, 2016 using date shifting without using the 0 parameter. That is the bug I'm attempting to report. Without the ability to go back a single day using date shifting, the entire concept feels broken. Appreciate you taking the time to explain everything to me. I'll continue to use the workaround you provided as it appears to correctly handle date shifting. Thanks, NathanRe: Bug: Date Function Issue
v-haibl-msft, Thanks for getting an answer and a workaround for this defintely helped out. However, I'm not sure I'm completely following on the provided resolution. It sounds like the DATE function in DAX should only work with valid dates, but the DATE DAX function follows the same rules for date shifting as the Excel version with the exception of the 0. Along with the Microsoft documentation using the date shifting features in the examples for the DATE DAX function. So for instance, the below DAX calculation would produce a valid date of Decemeber 30, 2016. Measure = DATE(2017, 1, -1) Does this mean the above calculation would not be supported? Or just the case when the day parameter is zero? Thanks for all the help, NathanBug: Date Function Issue
There's an issue with the DATE function supporting 0 day, which should represent the last day of the previous month. Measure = DATE(2017, 1, 0) The expected outcome on this should be December 31, 2016, but instead an error displays stating "An argument of function 'DATE' has the wrong data type or the result is too large or too small".Power BI Desktop Direct Query What-If Analysis
I'm attempting to add a column that will provide me the last 2 weeks of sales. I've seen a few other posts where users were attempting to do this, but I'm unable to follow the same method as I'm using Direct Query (working with large datasets so not reasonable to change). Here's the critera I'm attempting to work within (assuming it's possible): The "Current Date" is dynamic so it must be calculated based on the data set. Right now, I'm attempting to max the date on the data set, but this prevents me from using it in other measures. The Last 2 weeks of sales would be another column so I can see current sales and Last 2 Week sales side-by-side. So, based on the above critera I've been unable to create anything useful. Is there a better way to approach this problem that I'm missing? Here's an example of the dataset and what I'd like to have: Based on the dataset above, below is what I'm attempting to do. It obviously does not work, but should help clarify what I'm attempting to do. L2W Sales = CALCULATE( SUM(dataset[Sales]), TOPN(2, dataset, dataset[Date], DESC) )
