Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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!

 

amitchandak
Super User
Super User

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.