Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Stachu
Community Champion
Community Champion

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 🙂

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

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

Anonymous
Not applicable

Thank you for the great advice!

Stachu
Community Champion
Community Champion

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 🙂

Anonymous
Not applicable

this worked, thanks!!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors