Forum Discussion

CJMolin's avatar
CJMolin
Icon for Helper II rankHelper 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 e...
Need Help
  • v-yulgu-msft's avatar
    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

v-yulgu-msft's avatar
v-yulgu-msft
Icon for Microsoft Employee rankMicrosoft 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

CJMolin's avatar
CJMolin
Icon for Helper II rankHelper II
8 years ago

 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!