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 the query?

 

Thanks,

 

Changlin

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

6 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Changlin ,

    Create a table like

     

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

     

     

    • Changlin's avatar
      Changlin
      Frequent Visitor
      4 years ago

      Thanks for answering, with your query I got the following

      But I need the first day of month, so I made some changes with the query

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

      Then I got the result that I needed

      Thank you so much!

       

  • Jos_Woolley's avatar
    Jos_Woolley
    Solution Sage
    4 years ago

    Alternative for Power Query:

     

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

     

    I have hard-coded your starting date (2021,8,1) here, though you could change this to a dynamic reference if desired.

    Regards

     

     

    • Changlin's avatar
      Changlin
      Frequent Visitor
      4 years ago

      Thanks for answering, this power query works great!

      But if I want to keep both the first of current month and current date, how to do it?

      • 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