Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
hi Everyone,
i am a newbie to PowerBi.
i have a data source that i have 2 columns. Period and ValueCol.
Period gives dates eg. 2/1/2016, 2/2/2016, 2/3/2016, 2/6/2016 ..... for all months in 2016. ValueCol has values for each date entry. i want to have it as the "desired outcome" as in the picture below
Solved! Go to Solution.
Hi, welcome to powerbi. There are two things to do:
1. Make sure you have formatted your date column as a date: Modelling Ribbon/Formatting/Data type
if you stop at this point, You can drag in the date column to say the table visual and PBI will auto group the date into year, quarter, month, day (you can also show all the dates by clicking the little drop down arrow on the values field for the visualisation and selcting [Date])
2. For a better model, you should create a date table. Either connect to your date table in SQL or the like or create one by clicking Modelling/Calculations/New Table and in the formula bar entering this piece of DAX:
Datekey = CALENDAR(DATE(2017,01,01),date(2017,12,31)).
There are other methods but this is the simplest. You can add on columns for years, months, days etc but this is a bare bones solution.
Cheers,
// If this is a solution, please mark as such
@Anonymous To add to what Sam said, here is a DAX table that will produce various aggregations of time, including the specific request of Year/Month. Follow the same steps Sam outlined to create the table. Link your date column to the date column in the new table, and you can use the YearMonthShort to produce your table.
Credit for this code goes to a different poster, I just can't find the thread.
Date =
ADDCOLUMNS (
CALENDAR (DATE(2017,1,1), DATE(2017,12,31)),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"DayOfWeekNumber", WEEKDAY ( [Date] ),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" )
)
Hi @Anonymous,
Is the column [Period] set to date type? If so, you can refer to this formula to create a calculated table. Since there is no such a date format like this: 'Feb-16' (Mon-Year), you might need to create a new string column to display date in this format.
Test Table = SUMMARIZE ( TableName, TableName[PResolveDate].[Year], TableName[PResolveDate].[Month], "Total", SUM ( TableName[Amount] ) )
Date = 'Test Table'[Year] & "-" & 'Test Table'[Month]
Best regards,
Yuliana Gu
Hi @Anonymous,
Have you resolved your issue? If so, please kindly mark the corresponding reply as an answer so that some other users can find the solution more easily. If you still have any question, please feel free to ask.
Best regards,
Yuliana Gu
Hi @Anonymous,
Is the column [Period] set to date type? If so, you can refer to this formula to create a calculated table. Since there is no such a date format like this: 'Feb-16' (Mon-Year), you might need to create a new string column to display date in this format.
Test Table = SUMMARIZE ( TableName, TableName[PResolveDate].[Year], TableName[PResolveDate].[Month], "Total", SUM ( TableName[Amount] ) )
Date = 'Test Table'[Year] & "-" & 'Test Table'[Month]
Best regards,
Yuliana Gu
Hi @v-yulgu-msft @Seth_C_Bauer @samdthompson
Is it possible to group the totals in a column so that it sums up all the values from one column into a new one by month and year, user & on a task without having to create a new table to achieve the outcome?:
@Anonymous To add to what Sam said, here is a DAX table that will produce various aggregations of time, including the specific request of Year/Month. Follow the same steps Sam outlined to create the table. Link your date column to the date column in the new table, and you can use the YearMonthShort to produce your table.
Credit for this code goes to a different poster, I just can't find the thread.
Date =
ADDCOLUMNS (
CALENDAR (DATE(2017,1,1), DATE(2017,12,31)),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"DayOfWeekNumber", WEEKDAY ( [Date] ),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" )
)
Hi, welcome to powerbi. There are two things to do:
1. Make sure you have formatted your date column as a date: Modelling Ribbon/Formatting/Data type
if you stop at this point, You can drag in the date column to say the table visual and PBI will auto group the date into year, quarter, month, day (you can also show all the dates by clicking the little drop down arrow on the values field for the visualisation and selcting [Date])
2. For a better model, you should create a date table. Either connect to your date table in SQL or the like or create one by clicking Modelling/Calculations/New Table and in the formula bar entering this piece of DAX:
Datekey = CALENDAR(DATE(2017,01,01),date(2017,12,31)).
There are other methods but this is the simplest. You can add on columns for years, months, days etc but this is a bare bones solution.
Cheers,
// If this is a solution, please mark as such
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |