Issue with filtering data on every first working day of month

[Anonymous]

Hi Mates,

I have a data which I want to load based on dates. If the Current day is 1st day of current month then I want to load last full month's data, and if current day  is other than the 1st day of current month I want to load Currentmonth's MTD data.

Now my concern is if the current day is fall with Sunday or Monday then what? In that case it will load only Current month's MTD data, but I still want to load last full month's data. So I want to build a logic that considers current month's 1st working day only.

I have built below code which doesn't fulfills this. Could anyone help me here? Thanks in advance.

I have below code to filter data - 

#"Filtered Rows" = Table.SelectRows(#"Sorted Rows",

each if (Date.From([Created On]) = Date.StartOfMonth(DateTime.LocalNow()))

then Date.IsInPreviousMonth([Created On])

else Date.IsInCurrentMonth([Created On])),

[v-yiruan-msft]

Hi @Anonymous ,

Could you please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[Anonymous]

Hi @v-yiruan-msft 

Did you get a chance to look into this ? Could you please help me here?

[Anonymous]

Here is the sample pbix and data file.

https://1drv.ms/u/s!AuY70WzIFdLNgQFfwZD9d1i1RIiT?e=22801u

[Anonymous]

Hi @v-yiruan-msft ,

Thanks for reply.

Here is the specific code that I have developed now but not working,

#"Filtered Rows" = Table.SelectRows( #"Changed Type",
each if (Date.From(DateTime.LocalNow()) = Date.StartOfMonth(DateTime.LocalNow()) and (Date.Day(Date.StartOfMonth(DateTime.LocalNow())) <> "Saturday" or Date.Day(Date.StartOfMonth(DateTime.LocalNow())) <> "Sunday")
or (Date.From(DateTime.LocalNow()) = Date.AddDays(Date.StartOfMonth(DateTime.LocalNow()), +1) and Date.Day(Date.AddDays(Date.StartOfMonth(DateTime.LocalNow()), +1)) = "Monday") or (Date.From(DateTime.LocalNow()) = Date.AddDays(Date.StartOfMonth(DateTime.LocalNow()), +2) and Date.Day(Date.AddDays(Date.StartOfMonth(DateTime.LocalNow()), +2)) = "Monday"))
then Date.IsInPreviousMonth([Created Date])
else Date.IsInCurrentMonth([Created Date]))

So the logic I want to build is to filter and load the data based on date column. So, when 'CurrentDate' is the 1st working date of month(Not falls on Saturday or Sunday) then I want to load whole 'LastMonth' data only, otherwise I want to filter and load whole 'CurrentMonth' data only. So it is possible that 1st Day of 'CurrentMonth' falls on Saturday or Sunday, in that case it should check that also and load the 'LastMonth' data only.

e.g. 1 =  If you check the first day of September'22 (09/01/2022) it falls on Thursday, So on that day it should load all data from LastMonth only based on 'Created Date' column from file and on other days from current month it should load all data from CurrentMonth.

e.g. 2 = If you check the first day of October'22 (10/01/2022) it falls on Saturday which is holiday, So if we run report on Monday then it will load the data for that CurrentMonth(i.e. October) only. But on Monday we need to load LastMonth(i.e. September) data as it's our first working day of month and 10/01/2022 went in weekend holiday.

I hope you have understood the issue and my requirements. Thanks.

[Anonymous]

Can someone help me here..?