M Query to pull only prior month data from HANA

[Anonymous]

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).

[Anonymous · Accepted solution]

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'

                                  )

               )

[Anonymous]

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))

[RobertPaegelow]

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]

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?

[RobertPaegelow]

No clue currently, sorry..