Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.
Hello,
I am trying to build a table in a PowerBI report where each row corresponds to a different date. If the current day is day 1st of the month, I want the table to show all the days of the previous month, from 1st to 31st. If the current day is 2nd, I want the table to show only the row corresponding to the 1st day of the current month.
Then, for every completed day of the current month, one row more should be added to the table (ex: if today is 21st of August, the table should be showing from 1st to 20th of August)
Should I approach this problem with Power Query? Any tip on how to do it?
Thanks a lot!
Solved! Go to Solution.
Hello - this will do it. Please add a new blank query with the script below. There are three different scenarios that you can use to test the result by commenting (adding two forward slashes) to varToday lines and removing them from one.
Script
let
// Test various scenarios
//varToday = Date.From ( DateTime.FixedLocalNow() ),
varToday = #date ( 2023, 8, 1 ),
//varToday = #date ( 2023, 8, 2 ),
Yesterday = Date.AddDays ( varToday, -1 ),
CurrentMonthStart = Date.StartOfMonth ( varToday ),
CurrentMonthEnd = Date.EndOfMonth ( varToday ),
PriorMonthStart = Date.AddMonths ( CurrentMonthStart, -1 ),
PriorMonthEnd = Date.EndOfMonth ( PriorMonthStart ),
TodayIsCurrentMonthStart = varToday = CurrentMonthStart,
minDate = if TodayIsCurrentMonthStart then PriorMonthStart else CurrentMonthStart,
maxDate = if TodayIsCurrentMonthStart then PriorMonthEnd else Yesterday,
ListDates = List.Dates(minDate, Number.From(maxDate) - Number.From(minDate) + 1,#duration(1,0,0,0)),
ListToTable = Table.FromList(ListDates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
ChangeType = Table.TransformColumnTypes(ListToTable,{{"Column1", type date}}),
RenameDate = Table.RenameColumns(ChangeType,{{"Column1", "Date"}})
in
RenameDate
Result for 8/1/23
Result for 8/21/2023
Result for 8/2/2023
Hi
let
varToday = #date(2023, 8, 21),
Yesterday = Date.AddDays ( varToday, -1 ),
YesterdayMonthStart = Date.StartOfMonth(Yesterday),
Result = Table.FromColumns(
{List.Dates(YesterdayMonthStart, Duration.Days(Yesterday-YesterdayMonthStart)+1, #duration(1,0,0,0))},
type table [Date = date])
in
Result
Stéphane
Hello - this will do it. Please add a new blank query with the script below. There are three different scenarios that you can use to test the result by commenting (adding two forward slashes) to varToday lines and removing them from one.
Script
let
// Test various scenarios
//varToday = Date.From ( DateTime.FixedLocalNow() ),
varToday = #date ( 2023, 8, 1 ),
//varToday = #date ( 2023, 8, 2 ),
Yesterday = Date.AddDays ( varToday, -1 ),
CurrentMonthStart = Date.StartOfMonth ( varToday ),
CurrentMonthEnd = Date.EndOfMonth ( varToday ),
PriorMonthStart = Date.AddMonths ( CurrentMonthStart, -1 ),
PriorMonthEnd = Date.EndOfMonth ( PriorMonthStart ),
TodayIsCurrentMonthStart = varToday = CurrentMonthStart,
minDate = if TodayIsCurrentMonthStart then PriorMonthStart else CurrentMonthStart,
maxDate = if TodayIsCurrentMonthStart then PriorMonthEnd else Yesterday,
ListDates = List.Dates(minDate, Number.From(maxDate) - Number.From(minDate) + 1,#duration(1,0,0,0)),
ListToTable = Table.FromList(ListDates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
ChangeType = Table.TransformColumnTypes(ListToTable,{{"Column1", type date}}),
RenameDate = Table.RenameColumns(ChangeType,{{"Column1", "Date"}})
in
RenameDate
Result for 8/1/23
Result for 8/21/2023
Result for 8/2/2023
You're welcome!!
Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!
Check out the October 2025 Power BI update to learn about new features.