1st month to yesterday

[#1]

Hi,

I m using set analysis for a month to date expression.

=If(Day(Today()) =1,
Sum({$<MonthID = {$(=Max(MonthID))}, Quarter = , [LinkId] = {"FIN"}, Month ={$(=month(Addmonths(Today(), -1)))}, Year ={$(=year(today()))}>} [Bill Value]* $(RevenueRate)),
Sum({$<MonthID = {$(=Max(MonthID))}, Quarter = , [LinkId] = {"FIN"}, Month ={$(=month(today()))}, Year ={$(=year(today()))}>} [Bill Value]* $(RevenueRate)))

This also returns data for the previous month on day one. However, due to the reporting environment refreshing some records for today have been returned I need to return all records up to yesterday. Can you please tell me how I can do it dynamically

Thanks in advance

Dave

[sunny_talwar]

What is a difference between MonthID and Month field in your database? Why do you have set analysis for both of them (and seems contradicting for the true part of the if statement)?

[#3]

not entirely sure...it's what our suppliers have told us to use. You helped me write this code a few weeks ago. It meets my original needs but may need tweeking so remove today data

[sunny_talwar]

Can you create a table box with MonthID and Month as dimensions and post its screenshot?

[#5]

My apologies the formula I use is

Sum({<[Sales Order Date]={">=$(=MonthStart(Today(),Day(Today())=1))"},
LinkId={'SAL'}

>} [Sales Ordered Value])

the document from our supplier says:

The same concept used in MonthID can be used for the field Quarter. We can easily and seamlessly create a lot of expressions for Point In Time Reporting after we’ve created the Calendar table with at least the following fields: Date, Year, Month, Quarter, MonthID and QuarterID.

the calendar table doens't coantin MonthID

[sunny_talwar]

So you don't want to see previous month data on day 1 of a month? Try this:

Sum({<[Sales Order Date] = {">=$(=MonthStart(Today())"}, LinkId={'SAL'}>} [Sales Ordered Value])

[#7]

I do need to see the previous months data on day one, so the expressions works fine for that. . |it also returns today data which I don't want

[sunny_talwar]

How about this:

Sum({<[Sales Order Date]={">=$(=MonthStart(Today(),Day(Today())=1))<= & $(=Date(Today() - 1))"}, LinkId={'SAL'}>} [Sales Ordered Value])