Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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".
Solved! Go to Solution.
You may use the following calculated column.
Column =
FORMAT ( 'Table'[CLOSE_DT], "MMMM yyyy" )
You may use the following calculated column.
Column =
FORMAT ( 'Table'[CLOSE_DT], "MMMM yyyy" )
@GunnerJDid I answer your question? Please mark it as solution then 🙂
Kind regards,
Djerro123
Proud to be a Super User!
You should create a date table in your data model.
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! 🙂
Proud to be a Super User!
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |