cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GunnerJ
Helper V
Helper V

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
Resolver III
Resolver III

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
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Top Solution Authors