Sum non existing dates in a table visual

[Oscarín]

Hello! I hope you're doing great!

I have an issue with a dax measure:

Table 1 is a table that has columns for hours, users, and dates (the date is limited weekly).

Table 2 is my pivot table, which is updated up to today's date.

Table 3 is my pivot table for users

I am using the following DAX measure to display assistance in a table visual. The measure will check if the user had registered hours on that day; if not, it will return an 8, and if there are hours, it will return the registered hours:

Tiempo ideal =

VAR WW = SUMMARIZE('Table1', 'table2'[Date], 'table3'[XC-SM],"TI",IF([Asistencia] > 0 , [Asistencia], IF([Tiempo directo] + [Tiempo indirecto] <> BLANK(),8)))

RETURN

SUMX(WW,[TI])

The problem is that when I filter with my pivot table for a date that does not exist in Table 1, these values appear blank.

I would like to know how to make the calculation work even if the date in Table 2 does not exist in Table 1.

Here´s an image of what is happening:

Thanks!

[Anonymous · Accepted solution]

Hi,@Oscarín Hello,@lbendlin thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below.
I am glad to help you.

Based on your description, it seems that you are having an issue with the calculated value being null when using the created measure.

When the date of 'table2' does not exist in 'table1', the measure calculation returns a null value!

I think it has to do with the environment in which the measure is being calculated.

When the date in table2 can't be found in table1, then the corresponding row context can't be found (the corresponding date data can't be found), so the result is returned as null.

Since I don't know your specific data, I made some simple test data by simulating the data myself.

Here is my test, you can refer to it

I suggest you to create a new dynamic calendar table with all the data (according to your description, the date is updated to the current point in time (today))
like this
Calendar = CALENDAR(DATE(2024,5,1),DATE(2024,7,1))

You can use the Today() function to make a new calendar table always update to the latest date

Calendar_today = CALENDAR(DATE(2024,5,1),TODAY())

Use the new calendar table as an intermediate bridge table to connect 'table1' and 'table2'

Here is my test data

I created a Sales table and an Expenses table to show the daily sales and expenses.

But the data in the sales table is missing, there are some date data that can be found in the expenses table (daily expenses amount) but not in the sales table (daily sales).
[Sales]table

[expense] table

I created a calendar table with all the dates from both tables

Relational linking using calendar tables

I created the MEASURE.

Calculate the daily spending amount, if there is no corresponding sales amount credited to the day's spending data, only the spending amount will be recorded, otherwise calculate the spending-sales (income)

M_result = 
 VAR table_=SUMMARIZE('Expenses','Expenses'[SalesDate],'Expenses'[SaleNum],'Sales'[SaleNum],"result",
        IF('Sales'[SaleNum]>0, 'Expenses'[SaleNum]-'Sales'[SaleNum],'Expenses'[SaleNum])
 )
 RETURN 
SUMX(table_,[result])

You can see that after connecting through the calendar table, even if you can't find the corresponding 'Sales'[SaleNum],M_ is able to calculate the result.

For example, in the first row: 40 - 64 = -24.

The last line 6/16/2024 doesn't have a corresponding 'Sales'[SaleNum], it just returns the current 'Expenses'[SaleNum].

If you could provide more non-sensitive data information, preferably .pbix files that don't contain sensitive data, it will be helpful to solve your problem, because the calculation environment also affects the calculation results of the MEASURE, please double-check your filtering conditions (including internal filtering of the MEASURES and external filtering of the fields, including the slicer, if the slicer exists)

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

To report on things that are not there you need to use disconnected tables and/or crossjoins