Forum Discussion
Post PatronSimple 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".
You may use the following calculated column.
Column = FORMAT ( 'Table'[CLOSE_DT], "MMMM yyyy" )
4 Replies
- JarroVGIT
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&" "*yearThis 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-with/td-p/51745
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! 🙂
CoreyPSolution Sage
You should create a date table in your data model.
- v-chuncz-msft
Community Support
You may use the following calculated column.
Column = FORMAT ( 'Table'[CLOSE_DT], "MMMM yyyy" )
