Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calculate days between two dates

Hi everyone,

 

I want to calculate the number of days between the first and last date of a customer. The current formula for the column is:

Number of days = 1 * Table[Date_end] - Table[Date_start]
 

This formula doesn't take Customer_ID into account. As a consequence, the sum and average of number of days is wrong. Some customers have many rows, and some customers just a few (see table).

And the formula should calculate one row for each customer. So, the outcome of the Sum should be 333 + 1581 = 1914 and the Average should be 957. But this isn't the case, since it takes the sum of every row. In which way can this be solved?

 

Customer_IDDate_endDate_startDays between dates
395231-7-20191-9-2018333
395231-7-20191-9-2018333
812131-3-20211-12-20161581
812131-3-20211-12-20161581
812131-3-20211-12-20161581
812131-3-20211-12-20161581
812131-3-20211-12-20161581
812131-3-20211-12-20161581
812131-3-20211-12-20161581
812131-3-20211-12-20161581

  

Need Help

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Anonymous 

    For Visual Use a measure like this

    AverageX(summarize(table,table[Customer_ID],"_Avg",average(Table[Days between dates])),[_Avg])

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

    hi Anonymous 

    This looks like a measure totals problem. Very common. See this post about it
    https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

    Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
    https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

     

    For your case, just create two measure as below:

    outcome of the Sum = 
var _table=SUMMARIZE('Table','Table'[Customer_ID],"_value",AVERAGE('Table'[Number of days])) 
return
SUMX(_table,[_value])
    outcome of the Avg = 
var _table=SUMMARIZE('Table','Table'[Customer_ID],"_value",AVERAGE('Table'[Number of days])) 
return
AVERAGEX(_table,[_value])

    Result:

    and here is sample pbix file, please try it.

     

    Regards,

    Lin

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    6 years ago

    Hi,

    Assuming number of days is a measure, try this measure to get the correct sum in the Grand/subtotal row

    =IF(HASONEVALUE(Data[Customer_ID]),[Number of days],SUMX(SUMMARIZE(VALUES(Data[Customer_ID]),Data[Customer_ID],"ABCD",1*(MAX(Data[Date_end])-MIN(Data[Date_end]))),[ABCD]))

    If this does not help, then share the link from where i can download your PBI file.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thank you, Ashish_Mathur 

       

      This formula is a little bit more work around compared to the other solutions, but the formula does work :). 

      In the formula is one small error; it is MIN(Data[Date_start]. So, to prevent confusing for others, the formula is as follows:

       

      =IF(HASONEVALUE(Data[Customer_ID]),[Number of days],SUMX(SUMMARIZE(VALUES(Data[Customer_ID]),Data[Customer_ID],"ABCD",1*(MAX(Data[Date_end])-MIN(Data[Date_start]))),[ABCD]))