cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
erinwatkilace Frequent Visitor
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
Stachu Super Contributor
Super Contributor

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!

Proud to be a Datanaut!

4 REPLIES 4
Stachu Super Contributor
Super Contributor

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!

Proud to be a Datanaut!

Highlighted
Super User
Super User

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

erinwatkilace Frequent Visitor
Frequent Visitor

Re: Filter sales for beginning of year through last month

Thank you for the great advice!

erinwatkilace Frequent Visitor
Frequent Visitor

Re: Filter sales for beginning of year through last month

this worked, thanks!!

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 6 members 3,234 guests
Please welcome our newest community members: