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 basisEg: Historical Mon week 1= 100Historical Mon week 2= 200Historical 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-226735812017-10-217037172017-10-2025535812017-10-1932935812017-10-1836036572017-10-1740135812017-10-1636036572017-10-158837292017-10-149837012017-10-1331035812017-10-1235536572017-10-1136236572017-10-1039036572017-10-0941035812017-10-089539982017-10-076536052017-10-0626836572017-10-0540236572017-10-0442337202017-10-0345436572017-10-0242136572017-10-016840942017-09-307936052017-09-2930136572017-09-2841237292017-09-2739737012017-09-2641238972017-09-2535435812017-09-246740292017-09-235440942017-09-2226936052017-09-2134839602017-09-2036040642017-09-1936540122017-09-184393701 Please help me!

CJMolin

Helper II

8 years ago

Solved

Historical comparison on weekday - level basis

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!

Need Help

v-yulgu-msft

8 years ago

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

4 Replies

Greg_Deckler
Community Champion
8 years ago
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
  Helper II
  8 years ago
  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
    Microsoft Employee
    8 years ago
    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

Forum Discussion

Historical comparison on weekday - level basis

4 Replies

Related content

Visualizing Historical Data

BI Tools Comparison

Historical Log support

Exchange Rates - Historical

Comparison Chart