Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a large monthly billing file dating back to January '15 - each row is a monthly billing record for a customer's product.
Example:
Date | Customer Number | Product Number | Customer Duration | Product Duration |
10/1/2016 | 10050 | 100 | 17 | 12 |
9/1/2016 | 10050 | 100 | 16 | 11 |
8/1/2016 | 10050 | 100 | 15 | 10 |
7/1/2016 | 10050 | 100 | 14 | 9 |
6/1/2016 | 10050 | 100 | 13 | 8 |
5/1/2016 | 10050 | 100 | 12 | 7 |
4/1/2016 | 10050 | 100 | 11 | 6 |
3/1/2016 | 10050 | 100 | 10 | 5 |
2/1/2016 | 10050 | 100 | 9 | 4 |
1/1/2016 | 10050 | 100 | 8 | 3 |
12/1/2015 | 10050 | 100 | 7 | 2 |
11/1/2015 | 10050 | 100 | 6 | 1 |
10/1/2015 | 10050 | 200 | 5 | 5 |
9/1/2015 | 10050 | 200 | 4 | 4 |
8/1/2015 | 10050 | 200 | 3 | 3 |
7/1/2015 | 10050 | 200 | 2 | 2 |
6/1/2015 | 10050 | 200 | 1 | 1 |
I filled out the two columns I want to create a measure for (Customer Duration & Product Duration). The file has thousands of different customers so that will need to be the lookup value I suppose, but how do I account for the time factor? I've linked a calendar table's Date Key to the Date in the above table if that helps.
Thanks!
Solved! Go to Solution.
Hi @ccsrtw,
In this scenario, you should be able to use DATEDIFF Function (DAX) to calculate the time duration. See my sample below.
I assume you have a table called "MyTestTable" like below.
Then you should be able to use the formula below to create "Customer Duration" and "Product Duration" column.
Customer Duration = VAR cNum = MyTestTable[Customer Number] RETURN DATEDIFF ( CALCULATE ( MIN ( MyTestTable[Date] ), FILTER ( ALL ( MyTestTable ), MyTestTable[Customer Number] = cNum ) ), MyTestTable[Date], MONTH ) + 1
Product Duration = VAR cNum = MyTestTable[Customer Number] VAR pNum = MyTestTable[Product Number] RETURN DATEDIFF ( CALCULATE ( MIN ( MyTestTable[Date] ), FILTER ( ALL ( MyTestTable ), MyTestTable[Customer Number] = cNum && MyTestTable[Product Number] = pNum ) ), MyTestTable[Date], MONTH ) + 1
Regards
I'm looking for two outputs - Customer Duration and Product Duration. I populated the table with the values I want outputted.
I suppose by time factor I'm referring to the dates - need the durations to have some sort of time intelligence where they count only the rows with a date prior to its own. I hope that clears it up a bit, please let me know otherwise!
Appreciate the help!
Hi @ccsrtw,
In this scenario, you should be able to use DATEDIFF Function (DAX) to calculate the time duration. See my sample below.
I assume you have a table called "MyTestTable" like below.
Then you should be able to use the formula below to create "Customer Duration" and "Product Duration" column.
Customer Duration = VAR cNum = MyTestTable[Customer Number] RETURN DATEDIFF ( CALCULATE ( MIN ( MyTestTable[Date] ), FILTER ( ALL ( MyTestTable ), MyTestTable[Customer Number] = cNum ) ), MyTestTable[Date], MONTH ) + 1
Product Duration = VAR cNum = MyTestTable[Customer Number] VAR pNum = MyTestTable[Product Number] RETURN DATEDIFF ( CALCULATE ( MIN ( MyTestTable[Date] ), FILTER ( ALL ( MyTestTable ), MyTestTable[Customer Number] = cNum && MyTestTable[Product Number] = pNum ) ), MyTestTable[Date], MONTH ) + 1
Regards
@v-ljerr-msft, that's exactly it! Thank you!
Another wrinkle that I discovered while working with the new formulas - sometimes a customer will skip a few months of billing. The way the formula is currently setup, those gaps still add up to the total duration. That is fine in the majority of cases, but I'm wondering if there is a way to adjust the formula to account for the gaps? In other words, any months with a billing prior to a gap would not count towards the duration.
Appreciate the help, this is great!
Hi @ccsrtw,
What do you mean about "how do I account for the time factor"? Could you be more precisely with this issue and post the expected result in your scenario?
Regards
Hi @ccsrtw
Can you please share the output you are expecting.
Cheers
CheenuSing
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |