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
GunnerJ
Post Patron
Post Patron

Simple Question - How to get dates to sum into one month?

I have a field called CLOSE_DT that shows the Year, Month and Day a service order was closed.

I created a copy of that field and formatted it to only show the year and month so I could show multiple levels of detail.

However, when I try and chart the data to show activity by YEAR/MONTH it still segments it out copying the daily values. 

 

The picture below better illustrates my problem. I would expect to have one grand sum for December 2018 but instead its still treating it like it's Dec 1st, Dec 2nd, Dec 3rd, etc. There's got to be a simple fix to this but I'm just not seeing it at the moment. 

The data type is "Date", format is "MMMM yyyy". 

Seperated Months.PNG

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

@GunnerJ 

 

You may use the following calculated column.

Column =
FORMAT ( 'Table'[CLOSE_DT], "MMMM yyyy" )

 

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
v-chuncz-msft
Community Support
Community Support

@GunnerJ 

 

You may use the following calculated column.

Column =
FORMAT ( 'Table'[CLOSE_DT], "MMMM yyyy" )

 

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@GunnerJDid I answer your question? Please mark it as solution then 🙂

 

Kind regards,

Djerro123





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

Proud to be a Super User!




CoreyP
Solution Sage
Solution Sage

You should create a date table in your data model.

JarroVGIT
Resident Rockstar
Resident Rockstar

By the looks of it, you actually didn't change the value of the copied column but changed the format. The underlying value is still the full date (of type, datetype). 

You could create a calculated column with the following dax:

CalcColumnMonthYear = 
VAR month = FORMAT(DATEVALUE("2018-" & Table[originaldatecolumn] & "-1") , "MMM")
VAR year = FORMAT(YEAR(Table[originaldatecolumn]), "####")
RETURN
month&" "*year

This will return a "December 2019" format column as TEXT and then you can indeed expect it to sum the grand total per month. 

 

To be honest; the best way to do this is to use a datetable, create a relationship from you original date column to that date table and use a date hierarchy in combination with a table visual. I recommend this question and answer: https://community.powerbi.com/t5/Desktop/Date-table-s-why-are-they-needed-and-problems-they-bring-wi... 

Alternatively google around on datetables in combination with powerbi and you will find a ton of blogs on how to use a datetable to get your desired result! 🙂

 

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂





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

Proud to be a Super User!




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.