I have a table set up to show me sales and other data for current month. I also was able to pull some year to date totals (Sales SUM YTD), and last year to to date (SALES SUM YTD LY), etc. These were tricky but I found a lot of help here to get it done.
Now the problem is that when I try to drill down to get to detail. I have some data points that are left out of the table because there are no values for the current month in the data. Each data record has a Category and a Sub Category. So for example...
Category A: Sales SUM (filtered to current month) $19,639.43, Sales SUM YTD $462,296
Sub Category 1: Sales SUM (filtered to current month) $19,639.43, Sales SUM YTD $424,187
Sub Category 2: Sales SUM (filtered to current month) $0, Sales SUM YTD $37,277.56
Sub Category 3: Sales SUM (filtered to current month) $0, Sales SUM YTD $832.16
When I trid to put these all in the same table and get the detail - I will see the correct TOTAL field "Sales SUM YTD" for Category A, but I will only see the detail of Sub Category 1. So then I have 1 record of $424,187, but a final Subtotal of $462,296 because the other lines with YTD data is hiding.
I can only determine the Sales SUM YTD values if I put them in a table withOUT the Sales Sum for current month. How can I get them to appear and just show zero value for the current month? I have tried the obvious things as follows.
1) I tried creating a formula based on another tip - It did not work.
Suggested Template
IF(ISBLANK(value); 0; value)
My Version
IF(ISBLANK(SUM('00 Master Data Merged'[Sales SUM])),"0",SUM('00 Master Data Merged'[Sales SUM]))
2) I tried in any available field in the visualations to change it to "Show Items with no data"
I think part of the issue is that I do not have any records at all for the current month if sales are zero. My data is structured as a record for each transaction. The "ISBLANK" formula does not work because I do not have blank records, I don't have records at all within the current month, except in the historical data (records dated prior to the current month).
3) Multiple variations of date filtering and date filtering within the formulas/measures to no avail
4) I saw a similar post where someone solved by changing the relationship in the dates table (Dates tables are used to create the YTD formulas) but I am not sure how to do that!? My Sales SUM YTD formula is
Sales SUM YTD = CALCULATE(
TOTALYTD(SUM('00 Master Data Merged'[Sales SUM]),'Calendar Date'[Date],ALL('Calendar Date')),
ALL('00 Master Data Merged'[DATE])
)
