Need to convert whole number to date to then create MTD, QTD and YTD calculations using DAX

[ianmonat]

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. 

This expression:

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!

 looking to turn TrnYear and Trn Month into dates

[SanderBeukers · Accepted solution]

You should try to get a date column from the source. If that is not possible you can try the following:
Although maybe not ideal, i often combine the year and month in a new column (make sure both columns are formatted as whole numbers)

COLUMNNAME = TrnYear&"-"&TrnMonth

This will be recognized as a date when changing the format, then you will be able to apply any date function you want. It default on the first day of the month, if you want, you could get the last day of the month with the EOMONTH() function.

[ikkew]

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'

[rahulv]

Hi Sander

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.?

Regards

Rahul

[ianmonat]

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.

attempting a YTD shipment calculation

[SanderBeukers]

I think it is best to invest in learning how to create a DateTable in your report. It allows you to do all sort of date filters/measures of your choosing. The following website explains the basics:

http://databear.com/2016/05/08/power-bi-tips-calculating-year-to-date-values/

It also explains some basics about MTS/QTD/YTD in combination with a date table. You can create a relationship with the date table based on the column you've just created.

[ianmonat]

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