cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tabil
New Member

Trouble formatting dates for T/F Check

I have a date column with LatestCompletion m/d/yy. I used table[LatestCompletion].[Month] to separate out the months and did the same with the years. I want to compare the month and year to see previous month's data, but I can't format the resulting [Month] column. It only gives me the months as full text (January, etc.) and I get an #ERROR! when I try to change the data type or use format(). Is there a better way to do this? 

tabil_0-1624026349526.png

 

1 ACCEPTED SOLUTION
v-kelly-msft
Community Support
Community Support

Hi  @tabil ,

 

Create a column similarly as below:

Month = MONTH('Table (2)'[Date])

And you will see:

vkellymsft_0-1624346166875.png

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

 

View solution in original post

2 REPLIES 2
v-kelly-msft
Community Support
Community Support

Hi  @tabil ,

 

Create a column similarly as below:

Month = MONTH('Table (2)'[Date])

And you will see:

vkellymsft_0-1624346166875.png

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

 

View solution in original post

amitchandak
Super User IV
Super User IV

@tabil , if you have date, best is to use time intelligence with date table

 

example

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
Previous year Month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth(dateadd('Date'[Date],-11,MONTH)))

 

 

Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors