Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I am really struggling on this. It seems like it should be easy, but I think I am missing something! 🙂
I need a table with a list of months starting April 2020 to today and have it automatically update with the new months as time goes on.
Any ideas?
Cheers
Ed
Solved! Go to Solution.
Hi Ed,
Try this Power Query solution:
let
Source =
List.Distinct(
List.Transform(
{ Number.From(#date(2020,4,1))..Number.From(Date.From(DateTime.LocalNow())) },
each Text.Combine(
{
Text.Start(Date.MonthName(Date.From(_)), 3),
Text.End(Text.From(Date.Year(Date.From(_))), 2)
}, "-"
)
)
),
convertToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
convertToTable
For this output:
Pete
Proud to be a Datanaut!
months = var t1 = CALENDAR(DATE(2020, 4, 1), TODAY()) var t2 = ADDCOLUMNS(t1, "time_y", format([Date], "yyyy"), "time_ym", format([Date], "yyyy-MM") ) var t3 = GROUPBY(t2, [time_y], [time_ym], "d1", minx(CURRENTGROUP(), [Date]), "d2", maxx(CURRENTGROUP(), [Date])) return t3
Hi Ed,
Try this Power Query solution:
let
Source =
List.Distinct(
List.Transform(
{ Number.From(#date(2020,4,1))..Number.From(Date.From(DateTime.LocalNow())) },
each Text.Combine(
{
Text.Start(Date.MonthName(Date.From(_)), 3),
Text.End(Text.From(Date.Year(Date.From(_))), 2)
}, "-"
)
)
),
convertToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
convertToTable
For this output:
Pete
Proud to be a Datanaut!
months = var t1 = CALENDAR(DATE(2020, 4, 1), TODAY()) var t2 = ADDCOLUMNS(t1, "time_y", format([Date], "yyyy"), "time_ym", format([Date], "yyyy-MM") ) var t3 = GROUPBY(t2, [time_y], [time_ym], "d1", minx(CURRENTGROUP(), [Date]), "d2", maxx(CURRENTGROUP(), [Date])) return t3
Hi, @eggwald
use calender()
create a new table in pbi desktop and pasete this dax
CalendarTable = CALENDAR(DATE(2020, 4, 1), TODAY())
Proud to be a Super User!
Thanks @rubayatyasmin, is there a way to get it to just show the months?
EG:
Apr-20 |
May-20 |
Jun-20 |
Jul-20 |
....etc
from the data view select the column and check for format option in the ribon. Select your desired format for that. Or use FORMAT DAX function.
refer: https://learn.microsoft.com/en-us/dax/format-function-dax
Proud to be a Super User!