cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
BobBI Member
Member

Re: dynamic date

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

Super User
Super User

Re: dynamic rolling date (last 7 days)

@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 Datanaut !





9 REPLIES 9
Tom Regular Visitor
Regular Visitor

Re: dynamic rolling date (last 7 days)

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"

 

BobBI Member
Member

Re: dynamic date

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

Re: dynamic rolling date (last 7 days)

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

 

 

Tom Regular Visitor
Regular Visitor

Re: dynamic rolling date (last 7 days)

Did you put it in the advanced editor?

advancededitor.png

 

 

 

Super User
Super User

Re: dynamic rolling date (last 7 days)

@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 Datanaut !





Anonymous
Not applicable

Re: dynamic rolling date (last 7 days)

Yep Tried that. Didn't work.

Anonymous
Not applicable

Re: dynamic date

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

Re: dynamic date

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

Re: dynamic rolling date (last 7 days)

Hi Pattemmanohar,

 

This was helpful.

 

Thanks! 

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 6 members 4,445 guests
Please welcome our newest community members: