Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to Solution.
You can do this in Edit query mode. Here below are the screen shots.
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 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))
Proud to be a PBI Community Champion
You can do this in Edit query mode. Here below are the screen shots.
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
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
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
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"
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))
Proud to be a PBI Community Champion
Hi Pattemmanohar,
This was helpful.
Thanks!
Did you put it in the advanced editor?
Yep Tried that. Didn't work.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |