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.
Hello,
I have a table (Invoice) that has customer account number (customerAccountNumber), invoice total (TotalAmountBase) and date of purchase (InvoiceDate). I am trying to calculate a few columns to ultimately come up with a distinct count of customers and revenue that have purchased in the past R12 months and also in the R24-R12 month time period. I want this to dynamically change with the date. This is what I have:
R12DistinctCustomer =
Solved! Go to Solution.
I was able to manage two new measures that compute R12 revenue and Previous R12 revenue. I then added the following measure to compute the total if a customer purchased in R12 and in Previous R12 time period. The new measure will return a zero if they did not purchase in BOTH timeframes. Just in case someone is looking to do the same.
R12ANDPRIORR12REVENUE =
@DylanJohanssen , better to create measure in such case
Rolling 12 Sales =
var _max = maxx(allselcted(date),date[date]) // or today()
var _min = date(year(_max), month(_max)-12,1)
return
CALCULATE(SUM(Sales[Sales Amount]),filter(date, date[date] <=_max && date[date] >=_min))
Rolling 12 before 12 Sales =
var _max1 = maxx(allselcted(date),date[date]) // or today()
var _max = date(year(_max1), month(_max1)-12,1)
var _min = date(year(_max), month(_max)-12,1)
return
CALCULATE(SUM(Sales[Sales Amount]),filter(date, date[date] <=_max && date[date] >=_min))
For the new column, you can sum up the data will add for 12 month in each row
Rolling 12 Sales column =
var _max =[date]
var _min = date(year(_max), month(_max)-12,1)
return
CALCULATE(SUM(Sales[Sales Amount]),filter(date, date[date] <=_max && date[date] >=_min))
Rolling 12 before 12 Sales column =
var _max1 = [Date]
var _max = date(year(_max1), month(_max1)-12,1)
var _min = date(year(_max), month(_max)-12,1)
return
CALCULATE(SUM(Sales[Sales Amount]),filter(date, date[date] <=_max && date[date] >=_min))
I was able to manage two new measures that compute R12 revenue and Previous R12 revenue. I then added the following measure to compute the total if a customer purchased in R12 and in Previous R12 time period. The new measure will return a zero if they did not purchase in BOTH timeframes. Just in case someone is looking to do the same.
R12ANDPRIORR12REVENUE =
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 |
---|---|
47 | |
26 | |
19 | |
15 | |
10 |
User | Count |
---|---|
57 | |
50 | |
44 | |
21 | |
19 |