Formula to show sales through the end of current month

[cnpdx]

This formula works great to show sales through the end of last month:

MONTH END SALES = IF(TODAY()<ENDOFMONTH('_DATE TABLE CUSTOM'[DATE]),BLANK(),[SALES])

But how would the formula look if I want to show sales through the current month?

The problem is that there is future data in the system but I don't want to show it or calculate on it. I only want to show sales through the current month.

For example, the current date is 12/7/16. The system has invoices dated in January 2017 already, but I don't want those to be part of the calculation. 

SALES THROUGH CURRENT MONTH = ?

Thank you!

[th3h0bb5]

Do you want to pull in the current months and all prior months? If so, you could create a filter for your data that returns all dates up to the current month

currentMonthFilter = IF(NOW()<=[date], True, False)

If you want to pull in ONLY the current month, consider a filter like this:

currentMonth = IF(MONTH([date])+1 = MONTH(NOW()), True, False)

Then pull in your sales data and use this filter to only show what you want.

[itchyeyeballs]

Have you tried the built in timie intelligence functions?

Something like yeartodate may help - https://msdn.microsoft.com/en-us/library/ee634400.ax

[cnpdx]

Thanks for responding!

the result should be current month and all prior months.

when trying out your formula, all i get is sales for December 2016 and January 2017. but what i need is all past data through december

if you flip the sign <= to be this:  >=  it gives date through november, but i also need december to show up

also, how is the NOW function different from the TODAY function I was using?

[v-sihou-msft]

@cnpdx

I'm not quite clear about your question. If you just need to show all the past data up to today, you just need to compare the date with TODAY().

Column  = IF('Table'[Date]<=TODAY(),'Table'[Amount],BLANK())

Regards,

Simon Hou

[cnpdx]

@itchyeyeballs

@v-sihou-msft

Thanks for responding!

TODAY() is problematic because there can be invoices dated later in the month already in the system. 

For example, today is is 12/20, invoices dated 12/26 are already made and you want to see those. But there are also invoices in the system dated in January 2017 that I don't want to see. So to recap, we want to see all transactions through the end of current month, and filter out future months. 

TOTALYTD() has the same issue, it wouldn't show a 12/26 invoice.

[Vvelarde · Accepted solution]

@cnpdx

You can obtain the last day of current month with:

LasDayofCurrentMonth = EOMONTH(TODAY(),0)

[cnpdx]

@Vvelarde

That works!

LasDayofCurrentMonth = EOMONTH(TODAY(),0)