Calculate total amount between dates where there is a page filter date

[Racquel]

Hi,

I have a table with 3 main columns:

Date Paid, Amount, Maturity Date.

This table 'Date Paid' column has a relationship to the calendar Table 'Date'.

There are three possible filters on all pages from the calendar Table - Financial Year, Week Ending and Month (YYYY-MM) .

I want to create a measure that will give me the total amount where:

Date paid is before the Week ending and Fin Year date but Maturity date is after the week-ending date.

In other words, if I select the week ending 09/06/23, the amount I will see will be everything before that date (highlighted light orange on the Date paid) and where the maturity date is after that date (highlighted light orange on the Maturity date column) = $400.00 (highlighted orange on the amount column)

Thank you

[rubayatyasmin]

Hi, @Racquel 

you can use CALCULATE function to achieve your goal. 

refer to this doc: https://learn.microsoft.com/en-us/dax/calculate-function-dax

as the expression, you use sum to sum up the amount. 

then filter your table and apply the condition you have. Something like:

CALCULATE( SUM(Table[Amount]), FILTER( Table, Table[Date Paid] <= MAX(Calendar[Date]) && Table[Maturity Date] > MAX(Calendar[Date]) ) )

[Racquel]

When I use the Week End page filter, I only get amounts if the dates are within that week.

It does not pick up any amounts where the Date paid is before but the maturity date is after.

[rubayatyasmin]

I just gave you an idea how you can achieve the goal. if you want exact solution you might want to a demo PBIX file if you can. I will be happy to help. 

[Racquel]

Thank you for your idea.

Please see link below for demo.

PBIX file:

https://www.dropbox.com/s/vgitxohkhun1hoy/Demo.pbix?dl=0

XLSX file:

https://www.dropbox.com/scl/fi/mi4e1pjq371cjjy5qm3e9/Demo.xlsx?rlkey=ke7imya7h9tlt3phebrwnzzys&dl=0

Thank you kindly 🙂

[rubayatyasmin · Accepted solution]

the measure I provided works just fine. I checked it now. 

this is when page filter is applied instead of slicer. 

left side table is with my measure, the result you wanted and the right side is just the table visual to validate the result. Note: The amount column aggregation is set to don't aggregate. 

fun part: works perfectly fine when the relationship is inactive 

[Racquel]

Thank you @ rubayatyasmin.

I figured out why I couldn't get the same result as you. I had added a relationship between the Calendar and the data table. Once I deleted this, the result was the same as yours. Very happy for your help, thanks!