Not include the max record based on the date of the month

[svishwanathan]

Good Afternoon

I am working with the Accounting team and they have fluctuating reporting cycle. for one of the reports, i am directly querying the database. The issue I am facing is that between 10 and 22 of each month, people are constantly updatign and deleting records. The data is  stable between after 22 thru 9 of next month.  

 I want to create a Sales to Date measure that doesnt consider the most recent update to the table during 10-22. The report are set for daily refresh so I want to work on a measure

Period                                          Sale

Jan 2018                                        200

Feb 2018                                         100

March 2018                                      300

April 2018                                           400

Desired Sales to Date Measure =1000 but would be 600 if the report was viewed between 10 thru 22 april 

Not sure if i did a good job of explaining the problem...I cant think of anyway to tweak the sql query ( my sql skills are less that ordinary)  

Swati

[v-yuta-msft]

Hi svishwanathan,

Based on your description,  you are using direct query mode to connect to the database in power bi service and during 10~22 of a month, the data in measure is not correct, right?

Please check if the measure on Sale is corret and also check if all data in your database have been loaded into power bi because you said the database is often been updated.

Regards,

Jimmy Tao