Missing Sales Month in Pivot Table for Measure Containing TOTALYTD

[Anonymous]

Dear Members,

I have 2 tables:

1. Sales Transactions for 2019 (the last month is July);

2. Calendar with unique dates from 01/01/2019 until 31/12/2019.

The above-mentioned tables are linked by date columns.

All I need to do is to calculate the year to date sales, i.e. from Jan to Jul 2019, since this is the last month containing sales. Therefore, my aim is to only display on a PIvot Table sales by customers and only those months when there were sales. I'm applying the formula =IF(MAX(dCalendar[Date])<=LASTNONBLANK(all('Sales Transactions'[Order Date]),1),TOTALYTD([Sales (in USD)],Calendar[Date])). Then I created a Pivot Table in Excel. However, the Pivot Table only displays Jan-Jun, but July is missing. I don't have a clue what's the issue here.

Assistance to the issue above would be highly appreciated.

[amitchandak]

I think first of all you remove the filter of last not blank and check if July is coming.

If so put the TotalYTS into a VAR and use if not top of IT

YTD Sales =
Var _sales = TotalYTD(...)
Return 
IF(not(isblank(_sales),_sales,blank())

Or put this into a calculate to filter blank

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
Thanks.

[Anonymous · Accepted solution]

Thank You very much for Your response. Highly appreciated. I wrapped up the LASTNONBLANK into EOMONTH0, and it did the trick.