Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Moving average excluding weekends and holidays

I have a table within PowerPivot currently that tracks a count of customers through our sales pipeline. From (by sales location) first interaction to charged sale. So far, I’ve creates a moving 5 day...
Need Help
Anonymous's avatar
Anonymous
Not applicable
7 years ago

AlB

 

The table I will use for calculations does not contain weekends or holidays.

AlB's avatar
AlB
Icon for Community Champion rankCommunity Champion
7 years ago

Anonymous

Ok so I got it all wrong from the beginning then.

But if it's so, what is the problem?? you just take the last five dates present in the table from the date in the current row. You do not have to skip anything then.

  • AlexisOlson's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    7 years ago

    That's an interesting approach, but you have to be careful generalizing it. Indexing the workdays is a good idea, but I'd recommend doing the indexing on your dedicated date table rather than trying to deal with ranking duplicate dates in your fact table.

     

    Another approach would be to use the TOPN function as I suggested here.

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Hi Anonymous

    Have a look at this for a nice explanation on how to do it.

    Cheers

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    AlB

     

    I guess your right. How would I go about doing that within DAX? Is there an expression I'm missing here, or just a total mental block?

     

    Thanks!