Need help to Calculate Totals with Period Slicer When Date in different Column has Nulls

[adentler]

I am new to PBI and DAX and am looking for some help here. Thank you in advance for your input. 

I have a dataset that has the following data (reduced for this example):

Invoice Amount		Invoice Received Date	Invoice Paid Date (w null)	Invoice Paid Date (modified)
$           1,357.24		11/19/2024	12/31/2024	12/31/2024
$           2,143.57		12/5/2024	null	1/1/1900
$           2,356.14		1/5/2025	2/17/2025	2/17/2025
$           3,579.15		3/28/2024	4/16/2025	4/16/2025
$           1,478.69		2/2/2025	3/19/2025	3/19/2025
$           3,265.96		3/10/2025	null	1/1/1900

Using Measures and Switches, I am trying to calculate MTD, QTD, YTD, All Years, and Last Year total values for Invoiced, Paid, and Outstanding.

I created a Period table and a measure for selecting the Period in a slicer (This works for me):

1.  

Period	Period_ID
MTD	1
QTD	2
YTD	3
Last Year	4
All Years	5

1. Selected Period = MIN(Period[Period_ID])

I have successfully been able to calculate each of these periods for Total Invoiced when selected using the following Measures:

Invoiced = SUM('Table'[Invoice Amount])

Period Invoiced =

SWITCH([Selected Period],

1, TOTALMTD([Invoiced], DATESMTD('Table'[ Invoice Received Date ])),

2, TOTALQTD([Invoiced], DATESQTD('Table'[ Invoice Received Date ])),

3, TOTALYTD([Invoiced], DATESYTD('Table'[ Invoice Received Date ])),

4, TOTALYTD([Invoiced], PREVIOUSYEAR(DATESYTD('Table'[ Invoice Received Date ]))),

5, [Invoiced]

)

However, using the same logic as I did in the Invoiced measures (above), I am not able to calculate Paid, or Outstanding. I only return a number for the Total Paid or Total Outstanding for All Years, but the value does not change for the other selected periods.The only difference is I am using a different date column that had nulls. I attempted to account for the nulls by replacing the values with 1/1/1900. Below are the measures I used for Outstanding and Paid:

Outstanding =

CALCULATE(

    SUM('Table'[Invoice Amount]),

    'Table'[Invoice Paid Date (modified)] = DATE(1900, 1, 1)

)

Period Outstanding =

SWITCH([Selected Period],

1, TOTALMTD([Outstanding], DATESMTD('Table'[Invoice Paid Date (modified)])),

2, TOTALQTD([Outstanding], DATESQTD('Table'[Invoice Paid Date (modified)])),

3, TOTALYTD([Outstanding], DATESYTD('Table'[Invoice Paid Date (modified)])),

4, TOTALYTD([Outstanding], PREVIOUSYEAR(DATESYTD('Table'[Invoice Paid Date (modified)]))),

5, [Outstanding]

)

Paid =

CALCULATE(

    SUM('Table'[Invoiced Amount]),

    'Table'[Invoice Paid Date (modified)] <> DATE(1900, 1, 1)

)

Period Paid =

SWITCH([Selected Period],

1, TOTALMTD([Paid], DATESMTD('Table'[Invoice Paid Date (modified))])),

2, TOTALQTD([Paid], DATESQTD('Table'[Invoice Paid Date (modified))])),

3, TOTALYTD([Paid], DATESYTD('Table'[Invoice Paid Date (modified))])),

4, TOTALYTD([Paid], PREVIOUSYEAR(DATESYTD('Table'[Invoice Paid Date (modified))]))),

5, [Paid]

)

Using the slicer and the above measures, I should return the values in the table below. Which I do, but only for the Total Invoiced for the selected period and not Total Outstanding or Total Paid.

Totals:	Invoiced	Paid	Outstanding
MTD	$                        3,265.96	$              1,478.69	$              3,265.96
YTD	$                        7,100.79	$              7,413.98	$              3,265.96
QTD	$                        7,100.79	$              7,413.98	$              3,265.96
All Years	$                      14,180.75	$              8,771.22	$              5,409.53
Last Year	$                        7,079.96	$              1,357.24	$              2,143.57

I know this was a long post and I hope I have provided enough detail to get some clarification from this group. 

[Deku]

I would suggest calculation group, Zoe has a guide on how to set up time intelligence here

This would involve creating a date dimension. You would create a active relationship to invoice date and inactive relationships to paid date.

In the calculation items you would have to use selectedmeasure() to determine which measure is in scope. If invoice you can just use selectedmeasure(), wrapped by calculate with the time intelligence modifier. If paid you add userelationship(date[date], table[paid date]) to calculate() to activate the inactive relationship. For outstanding calculate both invoice and paid separately, then minus one from the other.

[adentler]

Thank you! I think a calculation group may be more than what I am looking for, but I am looking further into it. On a more basic, learning level.. would you be able to explain why the logic works referencing one date column (Invoice Received), but not another Date Column (Invoice Paid Date)?

[Deku · Accepted solution]

Because you have one date in your slicer but are then mixing with another date in your measure. You want a common dimension for date so the logic for the measure lines up

[adentler]

@Deku This makes so much sense. Thank you for taking the time and answering my question. I think I have the path forward between your idea of what to do and your explanation of what went wrong.