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

Filter sales for beginning of year through last month

Hello,

I have a sales table that shows sales by month, but also populates expected sales in the same column for previous months.  To filter out previous months I have used the following expressions:

DateswithSales = IF('Date'[Date]<=TODAY(),true,false) 
CurrentSales=
CALCULATE(sum(data_volume[SALES_BBLS]),FILTER('Date','Date'[DateswithSales]=true))
 
This works perfectly, however, the data is released a month behind.  
So even though my data is only actual sales through July,  August is showing expected sales since today's date is August 1, 2019.
Is there a way to change the filter to mark dates as true that are the previous month or less? 
I need the measure to just display sales January-July.  current month.PNG
Thank you for all your help. 
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User II
Super User II

Re: Filter sales for beginning of year through last month

if it's always full month until new data is released then you could use EOMONTH, like that:

DateswithSales = IF('Date'[Date] <= EOMONTH(TODAY(), -1), TRUE , FALSE)

it will return 31st July during all days of August, and start returning 31st August as of 1st September

Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Datanaut!

View solution in original post

4 REPLIES 4
Highlighted
Super User II
Super User II

Re: Filter sales for beginning of year through last month

if it's always full month until new data is released then you could use EOMONTH, like that:

DateswithSales = IF('Date'[Date] <= EOMONTH(TODAY(), -1), TRUE , FALSE)

it will return 31st July during all days of August, and start returning 31st August as of 1st September

Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Datanaut!

View solution in original post

Highlighted
Anonymous
Not applicable

Re: Filter sales for beginning of year through last month

First of all, please stop writing things like

DateswithSales = IF('Date'[Date]<=TODAY(),true,false) 

when this will do

DateswithSales = 'Date'[Date] <= TODAY()

The expression returns a logical value you're after so what's the use of IF?

 

Secondly, in the same vein, not

CurrentSales=
CALCULATE(sum(data_volume[SALES_BBLS]),FILTER('Date','Date'[DateswithSales]=true))

but

CurrentSales =
CALCULATE( 
    SUM( data_volume[SALES_BBLS]),
    'Date'[DateswithSales] -- this is already a logical expression
)

And thirdly, @Stachu has probably given you the correct measure but please remove the unnecessary clutter from it as shown above.

 

Best

Darek

Highlighted
Frequent Visitor

Re: Filter sales for beginning of year through last month

Thank you for the great advice!

Highlighted
Frequent Visitor

Re: Filter sales for beginning of year through last month

this worked, thanks!!

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors