Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Not applicable

Issue with filtering data on every first working day of month

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



Community Support
Community Support

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


Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Not applicable

Hi @v-yiruan-msft 


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


Not applicable

Here is the sample pbix and data file.!AuY70WzIFdLNgQFfwZD9d1i1RIiT?e=22801u

Not applicable

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.

Not applicable

Can someone help me here..?

Helpful resources

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.