Forum Discussion

sakamotothais's avatar
sakamotothais
Icon for Helper III rankHelper III
9 years ago
Solved

filter automatic date in power BI language M

Hi,

 

Need help.
I'm trying to leave my auto filter, so it updates itself.
My connection is direct and I am using the language M


I want it to always select the current date today, the date from yesterday d-1 and the date from seven days ago d-7. And always when the day is over and the next comes he updates alone.

I thought of something like:


date.adddays ([date], today ()) & date.adddays ([date], - 1) & date.adddays ([date], - 7)

 

It's possible?

 

thanks

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • MarcelBeug's avatar
    MarcelBeug
    8 years ago

    You can take a look at my post again.

     

    So my suggestion would be to adjust your code to take the date from your BookingDate; untested:

     

    let
    Source = Sql.Database("10.0.6.70,1433", "DailyBookings34"),
    DMBookings_BookingByLeg = Source{[Schema="DMBookings",Item="BookingByLeg"]}[Data],
    #"Filtered Rows" = Table.SelectRows(DMBookings_BookingByLeg, each Date.From([BookingDate]) = Date.From (DateTime.LocalNow ()) or Date.From([BookingDate]) = Date.AddDays (Date.From (DateTime.LocalNow ()) , -1) or Date.From([BookingDate]) = Date.AddDays (Date.From (DateTime.LocalNow ()), -7))
in
    #"Filtered Rows"

18 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    HI sakamotothais,

     

    You can enter query editor and add date filter on date column, then open the advanced editor and modify the filter formula to below sample bold part:

    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each [Date] = Date.From(DateTime.LocalNow()) or [Date]= Date.AddDays(Date.From(DateTime.LocalNow()),-1) or [Date] = Date.AddDays(Date.From(DateTime.LocalNow()), - 7))

     

     

    Regards,

    Xiaoxin Sheng

    • sakamotothais's avatar
      sakamotothais
      Icon for Helper III rankHelper III
      9 years ago

      Hi

       

      I just did not understand the step of and add date filter on date column

       

      let
      Source = Sql.Database("10.0.6.70,1433", "DailyBookings34"),
      DMBookings_BookingByLeg = Source{[Schema="DMBookings",Item="BookingByLeg"]}[Data],
      #"Removed Other Columns" = Table.SelectColumns(DMBookings_BookingByLeg,{"BookingID", "Status", "RecordLocator", "BookingPromoCode", "ChannelType", "SourceOrganizationCode", "BookingDate", "BookingDateAjustada", "PaxType", "BalanceDue", "ClassOfService", "CabinOfService", "FareClassOfService", "ProductClassCode", "FareBasis", "LegNumber", "DepartureDate", "CarrierCode", "FlightNumber", "DepartureStation", "ArrivalStation", "ChargeType", "ChargeAmount", "ChargeAmountBRL"}),
      #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "DepMonth", each Date.Month([DepartureDate])),
      #"Added Custom1" = Table.AddColumn(#"Added Custom", "Segment", each Text.Combine({[DepartureStation],[ArrivalStation]})),
      #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each [Status] >= 2 and [Status] <= 3),
      #"Added Conditional Column" = Table.AddColumn(#"Filtered Rows", "Source", each if [SourceOrganizationCode] = "AD" then "Internal" else "Agency" ),
      #"Added Custom2" = Table.AddColumn(#"Added Conditional Column", "BkMonth", each Date.Month([BookingDate])),
      #"Added Custom3" = Table.AddColumn(#"Added Custom2", "BkDay", each Date.Day([BookingDate])),
      #"Added Custom4" = Table.AddColumn(#"Added Custom3", "BkYear", each Date.Year([BookingDate])),
      #"Added Custom5" = Table.AddColumn(#"Added Custom4", "DepYear", each Date.Year([DepartureDate])),
      #"Added Custom6" = Table.AddColumn(#"Added Custom5", "DepDay", each Date.Day([DepartureDate])),
      #"Filtered Rows1" = Table.SelectRows(#"Added Custom6", each [FareClassOfService] <> "NC" and [FareClassOfService] <> "NR" and [FareClassOfService] <> "NU" and [FareClassOfService] <> "XA" and [FareClassOfService] <> "XH"),
      #"Filtered Rows2" = Table.SelectRows(#"Filtered Rows1", each [BalanceDue] <= 0),
      #"Added Custom7" = Table.AddColumn(#"Filtered Rows2", "Personalizado", each [FlightNumber]),
      #"Removed Columns" = Table.RemoveColumns(#"Added Custom7",{"Personalizado"}),
      #"Filtered Rows3" = Table.SelectRows(#"Removed Columns", each [CarrierCode] <> "CH"),
      #"Added Conditional Column1" = Table.AddColumn(#"Filtered Rows3", "Rule Revenue", each if [ChargeType] = 0 then "1" else if [ChargeType] = 1 then "-1" else if [ChargeType] = 7 then "-1" else "0" ),
      #"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column1", "Revenue", each if [Rule Revenue] = "0" then [ChargeAmount] else if [Rule Revenue] = "7" then [ChargeAmount] else if [Rule Revenue] = "1" then [ChargeAmount] else null ),
      #"Added Conditional Column3" = Table.AddColumn(#"Added Conditional Column2", "Segments", each if [ChargeType] = 0 then "1" else if [ChargeType] = -1 then "1" else if [ChargeType] = 4 then "0" else "1" ),
      #"Added Custom8" = Table.AddColumn(#"Added Conditional Column3", "BkHour", each Time.Hour([BookingDate])),
      #"Added Custom9" = Table.AddColumn(#"Added Custom8", "BkMinute", each Time.Minute([BookingDate])),
      #"Added Conditional Column4" = Table.AddColumn(#"Added Custom9", "BkQuarter", each if [BkMinute] <= 15 then "1" else if [BkMinute] <= 30 then "2" else if [BkMinute] <= 45 then "3" else "4" ),
      #"Filtered Rows4" = Table.SelectRows(#"Added Conditional Column4", each [BkHour] >= 0)
      in
      #"Filtered Rows4"

       

      Thanks for the feedback,

       

      Thanks

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        HI sakamotothais,

         

        Please refer to below steps:

         

        1. Find out the date column and choose filter.


        2. Input one date to click on ok to close the filter panel.

         

        3. Modify the filter formula.

         

        4. Click on  '√'  to finish edit.

         

        Regards,

        Xiaoxin Sheng