cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sandipde
Frequent Visitor

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

 

 

5 REPLIES 5
yingyinr
Community Support
Community Support

Hi @Sandipde ,

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.

Hi @yingyinr 

 

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

 

Here is the sample pbix and data file.

 

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

Hi @yingyinr ,

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.

Sandipde
Frequent Visitor

Can someone help me here..?

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.