Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello everyone,
I want to create a table that has only the year and month between 2 dates
2019-12-1 2024-12-31
my table needs to be like this
could someone please tell me what should I do
Solved! Go to Solution.
Hi @rima_ch ,
Check the new table formula.
Table = distinct(SELECTCOLUMNS(CALENDAR(DATE(2019,12,1),DATE(2024,12,31)),"Year",YEAR([Date]),"Month",FORMAT([Date],"MMMM")))
Best Regards,
Jay
Hi @rima_ch ,
Check the new table formula.
Table = distinct(SELECTCOLUMNS(CALENDAR(DATE(2019,12,1),DATE(2024,12,31)),"Year",YEAR([Date]),"Month",FORMAT([Date],"MMMM")))
Best Regards,
Jay
Another possible construction:
let
Start = #date(2019,12,1),
End = #date(2024,12,31),
Years = {Date.Year(Start)..Date.Year(End)},
#"Converted to Table" = Table.FromList(Years, Splitter.SplitByNothing(), {"Year"}),
#"Added Months" = Table.AddColumn(#"Converted to Table", "Month", each {1..12}),
#"Expanded Month" = Table.ExpandListColumn(#"Added Months", "Month"),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Month",{{"Year", Int64.Type}, {"Month", Int64.Type}}),
#"Added Date" = Table.AddColumn(#"Changed Type", "Date", each #date([Year], [Month], 1), type date),
#"Filtered Rows" = Table.SelectRows(#"Added Date", each [Date] >= Start and [Date] <= End),
#"Added Custom" = Table.AddColumn(#"Filtered Rows", "MonthName", each Date.MonthName([Date]), type text)
in
#"Added Custom"
You can keep or delete the extra columns as you wish.
Thank you a lot this is what I need but could you please tell me how to create it Dax and not power query
DAX is a bit simpler in this case:
MonthlyCalendar =
ADDCOLUMNS (
FILTER (
CALENDAR (
DATE ( 2019, 12, 1 ),
DATE ( 2024, 12, 31 )
),
DAY ( [Date] ) = 1
),
"Year", YEAR ( [Date] ),
"Month", MONTH ( [Date] ),
"MonthName", FORMAT ( [Date], "mmmm" )
)
Hi @rima_ch ,
Go to power query and paste this into the advanced editor:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTSN9Q3MjC0VNIBsY1BHCMTpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Start = _t, End = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Start", type date}, {"End", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Dates", each {Number.From([Start])..Number.From([End])}),
#"Expanded Dates" = Table.ExpandListColumn(#"Added Custom", "Dates"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Dates",{{"Dates", type date}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type1", "Year", each Date.Year([Dates])),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Month", each Date.MonthName([Dates])),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom2",{{"Year", type text}, {"Month", type text}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type2",{"Year", "Month"}),
#"Grouped Rows" = Table.Group(#"Removed Other Columns", {"Year", "Month"}, {{"det", each _, type table [Year=nullable text, Month=nullable text]}}),
#"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"det"})
in
#"Removed Columns"
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel
Proud to be a Super User!
Hi @rima_ch ,
Here are pics of steps in reverse order.
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel
Proud to be a Super User!
Thank you very much this is so helpful but could you please tell me how to create it Dax and not power query
Hi @rima_ch ,
Here is a great blog from Matt Allington. https://powerbi.tips/2017/11/creating-a-dax-calendar/
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel
Proud to be a Super User!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |