cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rima_ch
Frequent Visitor

undefined

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 

ho.PNG

could someone please tell me what should I  do 

1 ACCEPTED SOLUTION
v-jayw-msft
Community Support
Community Support

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")))

Capture.PNG

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

8 REPLIES 8
v-jayw-msft
Community Support
Community Support

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")))

Capture.PNG

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

AlexisOlson
Super User
Super User

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.

AlexisOlson_0-1637983133493.png

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" )
)

 

Nathaniel_C
Super User
Super User

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





Did I answer your question? Mark my post as a solution!

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
Advanced Editor paste codeAdvanced Editor paste codeHome tab in PQ new source blank queryHome tab in PQ new source blank queryIn PowerBI  Transform Data on home tabIn PowerBI Transform Data on home tabFinal tableFinal table





Did I answer your question? Mark my post as a solution!

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors