Obtaining a total count for the past 30 days

[successken]

Below is formula used in a powerpivot for a total number of orders for a single customer on the same day.

=CALCULATE(COUNTA(MVE_orders[orderid]), ALLEXCEPT(MVE_orders, MVE_orders[orderdate], MVE_orders[patientid]),MVE_orders[Job Type]<>"Doctors Order")

How can I expand this to obtain the total numbers of orders for the past 30 days including today?

Thank you in advance for your help with this!

 

[AlbertoFerrari]

Something like this should work:

[COLOR=#333333]=CALCULATE(
    COUNTA ( MVE_orders[orderid] ),
    ALLEXCEPT (MVE_orders, MVE_orders[patientid]) ,
    MVE_orders[Job Type]<>"Doctors Order",
[/COLOR][COLOR=#333333]    MVE_orders[orderdate] > MAX ( [/COLOR][COLOR=#333333]MVE_orders[orderdate] ) - 30 && [/COLOR][COLOR=#333333]MVE_orders[orderdate] <= MAX ( [/COLOR][COLOR=#333333]MVE_orders[orderdate] )[/COLOR][COLOR=#333333]
)

Not tested, but it should work. [/COLOR]

 

[successken]

I received the following error when I entered the code in my PowerPivot table:

"A function 'MAX' has been used in a True/False expression that is used as a table filter expression. This is not allowed"

Does the error have to do the the MVE_orders[orderdate] has a relationship that is tied to

 

[AlbertoFerrari]

Ops, MAX and CALCULATE are not supported as a syntax, this one is equivalent but should work:

=CALCULATE(
    COUNTA ( MVE_orders[orderid] ),
    ALLEXCEPT (MVE_orders, MVE_orders[patientid]) ,
    MVE_orders[Job Type]<>"Doctors Order",
    FILTER (
        ALL ( MVE_orders[orderdate] ),
        MVE_orders[orderdate]> MAX ( MVE_orders[orderdate] ) - 30 && MVE_orders[orderdate] <= MAX ( MVE_orders[orderdate] )
    )
)

Let me know how it goes.

Alberto

 

[successken]

No error, but the results are puzzling. I narrow the data to one patientid that has multiples orders through out the year. But i get the same result for each row which appears to be the total count 12/3/2012. The results I am looking for with this data set is 1/13/2012 - 1, 1/31/2012 - 4, 2/2/2012 - 5, 2/8/2012 - 6, 3/29/2012 - 9, 4/10/2012 - 4, 9/14/2012 - 1, 10/5/2012 - 3, 12/3/2012 - 7

orderid	orderdate
315795	1/13/2012
317246	1/31/2012
317250	1/31/2012
317248	1/31/2012
316852	2/2/2012
317779	2/8/2012
321964	3/29/2012
321940	3/29/2012
321943	3/29/2012
322836	4/10/2012
336852	9/14/2012
338457	10/5/2012
338458	10/5/2012
339562	12/3/2012
339563	12/3/2012
339568	12/3/2012
343088	12/3/2012
343090	12/3/2012
339564	12/3/2012
339565	12/3/2012

<tbody>
</tbody>

 

[AlbertoFerrari]

Oh, well... are you speaking about a calculated column or a measure? The code I provided works for a measure, not for a calculated column.
In a calculated column this should be the correct method:

 =CALCULATE(
    COUNTA ( MVE_orders[orderid] ),
    ALLEXCEPT (MVE_orders, MVE_orders[patientid]) ,
    MVE_orders[Job Type]<>"Doctors Order",
    FILTER (
        ALL ( MVE_orders[orderdate] ),
        MVE_orders[orderdate]> EARLIER ( MVE_orders[orderdate] ) - 30 && MVE_orders[orderdate] <= EARLIER ( MVE_orders[orderdate] )
    )
)

Anyway, please try to provide a workbook when you ask for such a specific formula, I am only guessing code here, no way to check it, unless I build your data model using test data, which is too time consuming to be efficient.

Alberto

 

[successken]

Oh, well... are you speaking about a calculated column or a measure? The code I provided works for a measure, not for a calculated column.
In a calculated column this should be the correct method:

 =CALCULATE(
    COUNTA ( MVE_orders[orderid] ),
    ALLEXCEPT (MVE_orders, MVE_orders[patientid]) ,
    MVE_orders[Job Type]<>"Doctors Order",
    FILTER (
        ALL ( MVE_orders[orderdate] ),
        MVE_orders[orderdate]> EARLIER ( MVE_orders[orderdate] ) - 30 && MVE_orders[orderdate] <= EARLIER ( MVE_orders[orderdate] )
    )
)

Anyway, please try to provide a workbook when you ask for such a specific formula, I am only guessing code here, no way to check it, unless I build your data model using test data, which is too time consuming to be efficient.

Alberto

My journey in PowerPivot was started in a mis-direction to where I focused on the columns to do the work and the measures were just to combined and organize the info. I have only recently discovered that the power tends to be in the measures. The original request was in regards to a calculated column and the code above worked great. Thank you for your help with this!!