Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Sum of values between two variable dates

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

 

 

Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    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

2 Replies

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    6 years ago

    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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    6 years ago

    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.