Historical comparison on weekday - level basis

[CJMolin]

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!

[Greg_Deckler]

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.

[CJMolin]

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.

[v-yulgu-msft · Accepted 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

[CJMolin]

 @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!