Hi, I'm ultimately trying to create MTD, QTD, and YTD calculations in Power BI Desktop using DAX and I'm stuck on the 'pre-step' of converting my date data (TrnYear and TrnMonth, see img) from Whole Numbers to Date values. I tried the DATE and DATEVALUE expressions and did not get the correct solution.
Year = DATEVALUE(InvMovements[TrnYear])
Results in this error:
Cannot convert value '2016' of type Text to type Date.
DATE is not working becasue it wants more arguments, but I just want the year
Year = DATE(InvMovements[TrnYear])
Results in this error:
Too few arguments were passed to the DATE function. The minimum argument count for the function is 3.
Another expression I used didn't throw an error but returned '1905' as the date value for the TrnYear value '2016'.
Any help would be very much appreciated!
Solved! Go to Solution.
Thank you! I was able to successfully calculate a new Date field using your formula.
However on to my next step of creating a new YTD Measure, I used a formula from a video tutorial on this site, and it didn't quite work, the shipments that I was measuring were way to high for the year. Maybe because the month was brought into the new Date field in addition to the year?
My formula is highlighted in the image, and TrnValue is what I'm trying to sum up YTD.
But I also need MTD/QTD/YTD for another metric, Orders (not shown in image), and the shipments and orders will be displayed. So I really need a metric agnostic time filter that I can display as a slicer on a dashboard, so a user can click back and forth displaying MTD/QTD/YTD and I'm not 100% sure I'm going about it the right way by creating a YTD Shipment Measure.
Thanks for sending that link, there was actually another link off of your link that had all the formulas listed to set up a nice Fiscal Date table in my database, which I think will help trmendously once I implement
Could you please help me in the case when we have only one column as YEAR, there is no month coulmn. At that point of time, how to convert that Year (originally as whole no.) into date data type.?
COLUMNNAME = Year&"-"&Month doesn't work for me.
It instead takes my year and adds the month (eg. january 2018 becomes 2019).
Transforming the result to a date displays '07-07-1905'