PBI Power Query M Code ---- Filter 1st Date Capture of the Month

[Joorge_C]

Hello All, Thanks in advance for your help and time.

I need to capture 1st Ocurrance only of Data with the DATE in a Month, I had a filter for less than 2nd Week of Month and less than 6th Day of month, to be able to get to that initial Snapshot in case there was a second close by entry.

The issue I have now, the entry was done on day 10, week 3. 

So I need to adjust my Filter to Capture the 1st Instance of Date in the Data by Month.

Any Ideas?

[v-kelly-msft]

Hi @Joorge_C ，

If I didnt understand wrong that you wanna create a slicer and show the 1st date of the Month,right?

If so,create a calculated column as below:

Column = MINX(FILTER('Table',MONTH('Table'[Date])=MONTH(EARLIER('Table'[Date]))),'Table'[Date])

Finally you will see:

For the related .pbix file, pls click here.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Joorge_C]

Hi @v-kelly-msft , Thanks for your inputs.

No Filters. This is on the M Code on the Import of Data

This is in the import of the data in power query, there is a daily snapshot in the data and I need to grab the first occurance of date in the month by month and filter out the rest of the Dates. The data is huge so this needs to be done in Query to the PBI is not impacted by having data in teh back end that is not needed.

[v-kelly-msft · Accepted solution]

Hi @Joorge_C ，

I made a sample table as below:

Then take the steps as below:

In query editor>add column>custom column,put the below M code inside:

Then right click on the column "Month", choose "group by "funtion and make the selection as below:

Finally you will see:

For the related .pbix file,pls click here.(You can also check the details in "advanced editor")

 
Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!