Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

count rows with datesbeween

Hi,

I have a table with orders incl startdate and enddate

for different types of orders we have a norm for the duration of the order in workdays

Now i want to count the number of orders that have a "good" or "bad" duratation

 

something like this:

 

on time calculation =
var norm = lookupvalue(norm[norm];ordertype;ordertype)

var ontime =

calculate

countrows(orders);

filter(
calendar;datesbetween;calendar;startdate;enddate;
calendar[isworkday] = 1) < norm)

return ontime

But this didn't work
Can someone help me?

Need Help

3 Replies

  • sturlaws's avatar
    sturlaws
    Icon for Resident Rockstar rankResident Rockstar
    6 years ago

    Anonymous 

     

    Could you please post some sample data in text-tabular format? Or, ideally, share the pbix (beware of confidential data)?

     

    Cheers,
    Sturla

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    You can populate a column in your fact. Please refer, How I have populated in dimension using the fact?

    Max Sales order id = Maxx(filter(sales,Sales[item_id]='Item'[Item ID]),Sales[Order Id]) 
Max customer = MAXX(filter(Sales,Sales[Order Id]='Item'[Max Sales order id] && Sales[item_id]='Item'[Item ID]),Sales[Customer ID])

     

    Even if you are sure there is one row, you need to have some aggregate function.

     

    The information you have provided is not making the problem clear to me. Can you please explain with an example. If possible please share a sample pbix file after removing sensitive information.
    Thanks