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
Anonymous
Not applicable

dynamic date

Hi,

I need a date table that has only the last dates of every month. Like the one below. (I wanted to create a parameter for period end date but that function doesn't seem to be available in power BI). Can you please help!

 

Thanks

 

31-Jan-18
28-Feb-18
31-Mar-18
30-Apr-18
31-May-18
30-Jun-18
31-Jul-18
31-Aug-18
30-Sep-18
31-Oct-18
30-Nov-18
31-Dec-18
31-Jan-19
28-Feb-19
31-Mar-19
2 ACCEPTED SOLUTIONS
BobBI
Resolver III
Resolver III

You can do this in Edit query mode.  Here below are the screen shots.

 

date table.JPGenddate.png 

 

and then right click on the Date header --> Remove duplicate , this will give you one row for each month as last day of the month.

 

Hope this helps,

SS

View solution in original post

@Anonymous Another way of doing this is.. Using "New Table" option with DAX below

 

_DimDateEOM = FILTER(CALENDAR("01-01-2018","31-12-2018"),[Date]=EOMONTH([Date],0))

image.png





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

Proud to be a PBI Community Champion




View solution in original post

9 REPLIES 9
BobBI
Resolver III
Resolver III

You can do this in Edit query mode.  Here below are the screen shots.

 

date table.JPGenddate.png 

 

and then right click on the Date header --> Remove duplicate , this will give you one row for each month as last day of the month.

 

Hope this helps,

SS

Anonymous
Not applicable

Hi Dynamic date,

 

That helped with the current database that I have. But in case where I dont have a date column and I need to create one, this solution wouldnt be helpful. If you have anything around creating a fresh date column lemme know.

 

Thanks

Anonymous
Not applicable

Hi Dynamic date,

 

That helped with the current database that I have. But in case where I dont have a date column and I need to create one, this solution wouldnt be helpful. If you have anything around creating a fresh date column lemme know.

 

Thanks

Anonymous
Not applicable

Create a blank query in the query editor and put this in the advanced editor, this will give you end of month dates up to todays date if you want to change the end date, update the DateTime.LocalNow() to the date you want.

 

let
    Source = #date(2013,1,1),
    Custom1 = List.Dates(Source, Number.From(DateTime.LocalNow())- Number.From(Source) ,#duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Inserted End of Month" = Table.AddColumn(#"Converted to Table", "End of Month", each Date.EndOfMonth([Column1]), type any),
    #"Removed Other Columns" = Table.SelectColumns(#"Inserted End of Month",{"End of Month"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns")
in
    #"Removed Duplicates"

 

Anonymous
Not applicable

I tried what you mentioned. It has created a table with one row with data as this formula. I dont see any dates.

 

 

@Anonymous Another way of doing this is.. Using "New Table" option with DAX below

 

_DimDateEOM = FILTER(CALENDAR("01-01-2018","31-12-2018"),[Date]=EOMONTH([Date],0))

image.png





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

Proud to be a PBI Community Champion




Anonymous
Not applicable

Hi Pattemmanohar,

 

This was helpful.

 

Thanks! 

Anonymous
Not applicable

Did you put it in the advanced editor?

advancededitor.png

 

 

 

Anonymous
Not applicable

Yep Tried that. Didn't work.

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.