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 pretty big problem which I couldnt really solve by my own.
I have a huge dataset with several KPI's. I would like to be able to benchmark the historical result of these KPIs each weekday with the current result.
Example:
I would like to measure how many contracts I have sent out in average the last 3 weeks on a week day basis
Eg:
Historical Mon week 1= 100
Historical Mon week 2= 200
Historical Mon week 3= 300
Historical Average past 3 weeks = 200
Current week Mon week 4 = 300, which means 50% increase compared to the previous 3 weeks average.
My dataset looks something like below. I'm first trying to get the acumulated sum of contracts sent out previous 14 days. But If i calculate the second columns it doesnt really exactly add up to the third columns aggregated total.
DAX measure used:
test = CALCULATE([sales];FILTER(ALL(Dim_Calendar[Date]);Dim_Calendar[Date]<=MAX('Application'[DateDocsCustomer])&&Dim_Calendar[Date]>MAX('Application'[DateDocsCustomer])-14);USERELATIONSHIP(Dim_Calendar[Date];'Application'[DateDocsCustomer]))
2017-10-22 | 67 | 3581 |
2017-10-21 | 70 | 3717 |
2017-10-20 | 255 | 3581 |
2017-10-19 | 329 | 3581 |
2017-10-18 | 360 | 3657 |
2017-10-17 | 401 | 3581 |
2017-10-16 | 360 | 3657 |
2017-10-15 | 88 | 3729 |
2017-10-14 | 98 | 3701 |
2017-10-13 | 310 | 3581 |
2017-10-12 | 355 | 3657 |
2017-10-11 | 362 | 3657 |
2017-10-10 | 390 | 3657 |
2017-10-09 | 410 | 3581 |
2017-10-08 | 95 | 3998 |
2017-10-07 | 65 | 3605 |
2017-10-06 | 268 | 3657 |
2017-10-05 | 402 | 3657 |
2017-10-04 | 423 | 3720 |
2017-10-03 | 454 | 3657 |
2017-10-02 | 421 | 3657 |
2017-10-01 | 68 | 4094 |
2017-09-30 | 79 | 3605 |
2017-09-29 | 301 | 3657 |
2017-09-28 | 412 | 3729 |
2017-09-27 | 397 | 3701 |
2017-09-26 | 412 | 3897 |
2017-09-25 | 354 | 3581 |
2017-09-24 | 67 | 4029 |
2017-09-23 | 54 | 4094 |
2017-09-22 | 269 | 3605 |
2017-09-21 | 348 | 3960 |
2017-09-20 | 360 | 4064 |
2017-09-19 | 365 | 4012 |
2017-09-18 | 439 | 3701 |
Please help me!
Solved! Go to Solution.
Hi @CJMolin,
From above example, which one is [Sales] column? Why do you need to calculate the acumulated sum of contracts in last 14 days?
I am not very clear about your requirement, please show us your ddesired result with examples. Based on my assumption, I created below formula to get the average value every 3 weeks.
WeekNum = WEEKNUM('Application'[DateDocsCustomer]) WeekDay = WEEKDAY('Application'[DateDocsCustomer],2) Average last three weeks = CALCULATE ( AVERAGE ( 'Application'[Sales] ), FILTER ( 'Application', 'Application'[WeekNum] <= EARLIER ( 'Application'[WeekNum] ) && 'Application'[WeekNum] > EARLIER ( 'Application'[WeekNum] ) - 3 && 'Application'[WeekDay] = EARLIER ( 'Application'[WeekDay] ) ) )
Best regards,
Yuliana Gu
Haven't looked at this enough to present a full solution, but I would point you to the WEEKNUM and WEEKDAY functions in DAX.
https://msdn.microsoft.com/en-us/library/ee634572.aspx
https://msdn.microsoft.com/en-us/library/ee634550.aspx
You should be able to combine those with the TODAY function to achieve what you are looking for.
Hi @Greg_Deckler,
Thanks. I am well aware of these functions. Although the problem I have is to get an average sum of [sales] the last x number of mondays,tuesdays etc etc.. As stated in the example in the description.
Hi @CJMolin,
From above example, which one is [Sales] column? Why do you need to calculate the acumulated sum of contracts in last 14 days?
I am not very clear about your requirement, please show us your ddesired result with examples. Based on my assumption, I created below formula to get the average value every 3 weeks.
WeekNum = WEEKNUM('Application'[DateDocsCustomer]) WeekDay = WEEKDAY('Application'[DateDocsCustomer],2) Average last three weeks = CALCULATE ( AVERAGE ( 'Application'[Sales] ), FILTER ( 'Application', 'Application'[WeekNum] <= EARLIER ( 'Application'[WeekNum] ) && 'Application'[WeekNum] > EARLIER ( 'Application'[WeekNum] ) - 3 && 'Application'[WeekDay] = EARLIER ( 'Application'[WeekDay] ) ) )
Best regards,
Yuliana Gu
@v-yulgu-msft Hi!
That was exactly what I was looking for.. What I forgot in my forumla was weekday=earlier(weekday)!
Very much appreciated.
Btw, what tool are you using to convert your DAX to a more readable way? Like I always write my calculations just in one huge row which is very hard to read afterwords 🙂
Thanks again!
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 |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |