Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

M Query to pull only prior month data from HANA

Hi All,

I’m trying to query a HANA view for only data from the month prior to the current month.  So if the current run date is 12/14/22, I’m looking for data with date values 11/01/22 thru 11/30/22. This will be used to refresh a Power BI report each month.

 

There are two fields that will determine the data that meets the criteria:

               PrimaryApproval

               FinalApproval

I’m looking for the correct m query syntax for:

WHERE "PrimaryApproval " date = Previous Month OR (“FinalApproval " = Previous Month AND "PrimaryApproval " is blank).

 

What I expect to be returned is all PrimaryApproval with dates 11/01/22 thru 11/30/22, and also FinalApproval with dates 11/01/22 thru 11/30/22 where the PrimaryApproval is blank (not populated).

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    This is what worked for me:

    WHERE

                   (

                                  (

                                                 "PrimaryApproval" > Add_Months(Last_Day(current_date), -2)

                                                 AND "PrimaryApproval" <= Add_Months(Last_Day(current_date), -1)

                                  )

                                  OR (

                                                 (

                                                                "FinalApproval" > Add_Months(Last_Day(current_date), -2)

                                                                AND "FinalApproval" <= Add_Months(Last_Day(current_date), -1)

                                                 ) 

                                                 AND "PrimaryApproval" = '00000000'

                                      )

                   )

5 Replies

  • RobertPaegelow's avatar
    RobertPaegelow
    Helper I
    3 years ago

    Something like this? 

     

    WHERE
    (
        "PrimaryApproval" >= Date.AddMonths(Date.EndOfMonth(DateTime.LocalNow()), -1)
        AND "PrimaryApproval" < DateTime.LocalNow()
    )
    OR (
        "FinalApproval" >= Date.AddMonths(Date.EndOfMonth(DateTime.LocalNow()), -1)
        AND "FinalApproval" < DateTime.LocalNow()
        AND "PrimaryApproval" = null
    )
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi Robert!

       

      Thanks for the quick response. Having an issue with "DATETIME". In my attempts before posting to this board, I thought I had a solution but got this same message as I get with your suggested code: 

      Also, won't what you provided return data from 11/1/22 thru 12/14/22 if it was executed today?

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    A little more information:

     

    We are using the _SYS_BIC schema. I would expect the statement below to get the previous month, but again, the 'DateTime' is an issue.

    Date.IsInPreviousMonth(Date.AddMonths(DateTime.FixedLocalNow(), -1))

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    This is what worked for me:

    WHERE

                   (

                                  (

                                                 "PrimaryApproval" > Add_Months(Last_Day(current_date), -2)

                                                 AND "PrimaryApproval" <= Add_Months(Last_Day(current_date), -1)

                                  )

                                  OR (

                                                 (

                                                                "FinalApproval" > Add_Months(Last_Day(current_date), -2)

                                                                AND "FinalApproval" <= Add_Months(Last_Day(current_date), -1)

                                                 ) 

                                                 AND "PrimaryApproval" = '00000000'

                                      )

                   )