Sum of values between two variable dates

[Anonymous]

Hello all,

In my Power BI Desktop file I try to create measures that calculate and outstanding amount between days of a due date. I want to create 4 measures:

1. Today to today -30 days
2. Today -31 days to today -60 days
3. Today -61 to Today -90 days
4. Today - 90 days and further.

I have to use two columns: "Due date" and "Outstanding amount". I created the formule for the "measure 1". The formula goes as follows:

0 -30 days = CALCULATE (

SUM ( 'debtchase-report'[Outstanding Amount] ),

FILTER (

ALL ( 'debtchase-report'[Due Date] ),

'debtchase-report'[Due Date]

> TODAY() - 30))

This formula works for me however I can't seem to find the formulas for the others measures I need to calculate. Can someone help me?

Thanks in advance!

Best regards

[Ashish_Mathur]

Hi,

Build a Calendar Table and create a relationship from the Due Date column of the debtchase-report Table to the Date column of the Calendar Table.  Write these measures

0-30 days = CALCULATE(SUM ( 'debtchase-report'[Outstanding Amount]),DATESBETWEEN(Calendar[Date],TODAY()-30,TODAY()))

31-60 days = CALCULATE(SUM ( 'debtchase-report'[Outstanding Amount]),DATESBETWEEN(Calendar[Date],TODAY()-60,TODAY()-31))

61-90 days = CALCULATE(SUM ( 'debtchase-report'[Outstanding Amount]),DATESBETWEEN(Calendar[Date],TODAY()-90,TODAY()-61))

90 days+ = CALCULATE(SUM ( 'debtchase-report'[Outstanding Amount]),DATESBETWEEN(Calendar[Date],MINX(ALL(Calendar),Calendar[Date]),TODAY()-91))

Hope this helps.

[v-lili6-msft · Accepted solution]

hi, @Anonymous 

You use the same logic for other measures as below:

Today -31 days to today -60 days = CALCULATE (
SUM ( 'debtchase-report'[Outstanding Amount] ),
FILTER (
ALL ( 'debtchase-report'[Due Date] ),
'debtchase-report'[Due Date]
> TODAY() - 60&&'debtchase-report'[Due Date]
<= TODAY() - 31))

Today -61 days to today -90 days = CALCULATE (
SUM ( 'debtchase-report'[Outstanding Amount] ),
FILTER (
ALL ( 'debtchase-report'[Due Date] ),
'debtchase-report'[Due Date]
> TODAY() - 90&&'debtchase-report'[Due Date]
<= TODAY() - 61))

Today - 90 days and further = CALCULATE (
SUM ( 'debtchase-report'[Outstanding Amount] ),
FILTER (
ALL ( 'debtchase-report'[Due Date] ),
'debtchase-report'[Due Date]<= TODAY() - 90))

If not your case, please share some simple sample data and expected output.

Best Regards,

Lin