Forum Discussion

Changlin's avatar
Changlin
Frequent Visitor
4 years ago
Solved

How to create this special date column?

Hi everyone,   I want to create a new column in Power BI like the following example: The previous rows are the first day of each month, but the last row is the current date.   How to write...
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    Changlin ,

    Create a table like

     

    Filter(calendar(Eomonth(today(), -12) +1, Today()), [Date] = eomonth([Date],0) || [Date] = today())

     

     

  • Jos_Woolley's avatar
    Jos_Woolley
    4 years ago

    Try:

    let
    Source = List.Generate(
        () => #date(2021, 8, 1), 
        each Number.From(_) < Number.From(Date.AddMonths(DateTime.LocalNow(), 1)), 
        each Date.AddMonths(_, 1), 
        each 
            if _ = DateTime.Date(Date.StartOfMonth(Date.AddMonths(DateTime.LocalNow(), 1))) then
                DateTime.Date(DateTime.LocalNow())
            else
                _
    )
in
    Source

    Regards

Jos_Woolley's avatar
Jos_Woolley
Solution Sage
4 years ago

Try:

let
    Source = List.Generate(
        () => #date(2021, 8, 1), 
        each Number.From(_) < Number.From(Date.AddMonths(DateTime.LocalNow(), 1)), 
        each Date.AddMonths(_, 1), 
        each 
            if _ = DateTime.Date(Date.StartOfMonth(Date.AddMonths(DateTime.LocalNow(), 1))) then
                DateTime.Date(DateTime.LocalNow())
            else
                _
    )
in
    Source

Regards

Changlin's avatar
Changlin
Frequent Visitor
4 years ago

Perfect.

 

I'm not familiar with power query, so I know should do some changes to condition logic before but not sure where to do it. When I see your new query, I undersatnd immediately.

 

Thank you so much!