Filter rows with IF Statement depending on 1st day of month or not

[AndreBel]

Hi,

I'm new to coding with Power Query M/DAX and to the PBI Community and was hoping to get some help on how to code for the following situation involving filtering rows with an IF statement. Any help would be greatly appreciated as I come to grips with coding with M and DAX in Power Query. Please bear with me as this is my first post and apologize for the long winded question 🙂

The Community has been a great resource so far, but I cannot find a similar solution for this. I have tried some code below, but because I cannot debug/step through it as I would with VBA or a Function in Excel, I'm not sure where it's failing, as it does not return my expected result. If there is a way to "step" through the code to see the results, that would be great. This is being done in Excel Power Query, but I believe it should be the same for PBI. 

Situation:

I have multiple years of production data, with each row containg a date with a quantity and would like to filter the rows as follows, depending on the current date:

• If the current date is the 1st of the month, then the assumption is I want to summarize all the data from the prior month ie Today is January 1, 2021, then get all the data for December 1-31, 2020.
• But if the current date is any from 2-31 of the month, then assume we want a MTD for the current month. ie Today is January 16, 2021, then filter all data for January 1-16, 2021.

My thinking on how this should be done with M in the Advanced Editor with my actual code below:

If Today()= 1st day of the Month

     Then Filter rows to include all rows from LastMonth only

     Else Filter rows to include all rows from ThisMonth only

My code:

let
    Source = Excel.Workbook(File.Contents("N:\test.xlsx"), null, true),
    #"Inv Movements_Sheet" = Source{[Item="Inv Movements",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(#"Inv Movements_Sheet",{{"Column1", type any}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type any}, {"Column6", type any}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}}),
    #"Removed Top Rows" = Table.Skip(#"Changed Type",1),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Top Rows",{"Column1"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Material", Int64.Type}, {"Material - Text", type text}, {"Batch", Int64.Type}, {"Posting date", type date}, {"Qty in KGs", Int64.Type}, {"MRP Controller", type text}, {"Alloy Char", Int64.Type}, {"MRP and Alloy", type text}, {"Storage location - Key (Not Compounded)", type text}, {"Storage location - Medium Text", type text}, {"Material Planning Project - Text", type text}, {"Material Plant View - Surface Finish (Text)", type text}, {"MRP Controller - Short Text", type text}, {"Paper Req.", type text}, {"Pretreat Type", type text}, {"Lube Type", type text}, {"PPID", Int64.Type}, {"PPID Desc", type text}}),
    #"Filtered Rows" = if DateTime.LocalNow() = Date.StartOfMonth(DateTime.LocalNow()) then Table.SelectRows(#"Changed Type1", each Date.IsInPreviousMonth([Posting date])) else Table.SelectRows(#"Changed Type1", each Date.IsInCurrentMonth([Posting date]))
in
    #"Filtered Rows"

I believe I'm having trouble with the date formats, as running the above today (January 1, 2021) results in no records (PQ appears to be filtering on data=ThisMonth in which there is no data yet, as we're always 1 day behind with posted prod data). My expectation would be for it to filter for all December 2020 data, but if run tomorrow (Jan 2), then it would return filtered data for Jan 1-2, 2021).

Any suggestions?

Thank you!

[v-alq-msft]

Hi, @AndreBel 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may create a measure like below.

Visual Control = 
var lastm = EOMONTH(TODAY(),-1)
var cm = EOMONTH(TODAY(),0)
return
IF(
    DAY(TODAY())=1,
    IF(
        FORMAT(SELECTEDVALUE('Table'[Date]),"yyyy-mm")=FORMAT(lastm,"yyyy-mm"),
        1,0
    ),
    IF(
        FORMAT(SELECTEDVALUE('Table'[Date]),"yyyy-mm")=FORMAT(cm,"yyyy-mm")&&SELECTEDVALUE('Table'[Date])<=TODAY(),
        1,0
    )
)

Then you need to put the measure in the visual level filter to display the result.

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jimmy801 · Accepted solution]

Hello @AndreBel 

if you don't need all other data in your model, Power Query is the way to go. Otherwise it would be better to make a measure in DAX. Here the solution in Power Query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTVMzTSMzJQitWJVjI2QOIYGOoBkZEhhGME48QCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Posting Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Posting Date", type date}},"de-DE"),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each if Date.From(DateTime.FixedLocalNow())=Date.From(Date.StartOfMonth(DateTime.FixedLocalNow())) then Date.IsInPreviousMonth([Posting Date]) else Date.IsInCurrentMonth([Posting Date]))
in
    #"Filtered Rows"

the problem with your code is that you are comparing Date-format with DateTime-format and this will give you always false. Surround your Date.StartOfMonth with a Date.From-statement. As you can see in my solution, you can integrate the if-function one Table.SelectRows-statement

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[AndreBel]

Hi Jimmy,

Thanks for your quick reply! This is what I needed and works like a charm. I figured it had something to do with the DateTime-format, but didn't know how to achieve this. Thank you also for showing me how to include the If stmt inside the Table.SelectRows stmt, as it certainly simplifies the code.

I see you used the DateTime.FixedLocalNow vs DateTime.LocalNow. I wasn't quite sure what the difference between the two was after reading through MS 'Power Query M' docs. So 'Fixed' would be the preferred one in most cases, as the other could potentially change during an extended query into the following day?

As mentioned in an earlier post, I will need to read up on how and why I would use a DAX measure instead of this M query as I am very new at this.

Thanks again!

[Anonymous]

Hi AndreBel,

I don't think M is the correct solution, for this you should try creating a separate column using Dax.

Check out the below page for time intelligence formula of PowerBI:

Time intelligence functions (DAX) - DAX | Microsoft Docs

If you can share a dummy data, I may be able to help you with setting up Dax formula for basic time intelligence feature.

Regards

Raj

[AndreBel]

Hi,

Thank you for your reply. I will need to explore this some more to see how this could work, as I don't fully understand how creating another column would help filter my data. I'll get back to you, if I feel this might be a viable solution.

Thanks!