Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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.
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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.