Closed

Powerpivot-DAX

I have a powerpivot orders table that contains records with three statuses Live, Expired and Cancelled. The records also include the number of product recipients for each order. I also have a date table (Dim Date). I have been trying to work out a way to be able to count the number of recipients at any point any particular point in time.

In terms of the information I have the following:

The current status of the order - live, expired of cancelled

The start date of the order

The expiry date of the order

The cancellation date (which will be after the start date but before the expiry date)

What I want to be able to achieve is to show how many recipients there were at any point in the data's history. So I have to be able to deal with the fact that a record that is now cancelled or expired would have been live prior to it's expiration or cancellation date. I also need to deal with the fact that some Live orders may be in the future and therefore should not be counted until they have reached their start date

So far I've come up with this:

CurrentLiveRecipients (old measure):=CALCULATE (

SUM ( CVM_dashboard_new_or_renewal_historic[UNIQUE_RECIPIENTS] ),

FILTER (

ALL ( CVM_dashboard_new_or_renewal_historic ),

CVM_dashboard_new_or_renewal_historic[Cancellation_Date]

>= TODAY ()

), FILTER (

ALL ( CVM_dashboard_new_or_renewal_historic ),

CVM_dashboard_new_or_renewal_historic[Expiry_Date]

>= TODAY () && CVM_dashboard_new_or_renewal_historic[STATUS]="Live"

),

FILTER (

ALL ( DimDate ),

DimDate[Date Value] <= MAX ( DimDate[Date Value] )

)

)

But I simply cannot get this to give me the numbers I am expecting in my pivot table. Instead it seems to give me a cumulative total as can be seen below.

Row Labels CurrentLiveRecipients (new measure)

2012 469

1 39

2 66

3 98

4 134

5 178

6 219

7 238

8 255

9 294

10 332

11 418

12 469

2013 2095

1 623

2 866

3 993

4 1160

5 1354

6 1544

7 1655

8 1762

9 1911

10 1999

11 2095

12 2095

2014 2369

1 2153

2 2179

3 2191

4 2214

5 2232

6 2266

7 2309

8 2322

9 2332

10 2351

11 2352

12 2369

Grand Total 2369

Skills: Data Processing, Excel, Visual Basic

See more: pivot table excel, excel pivot table, excel dax, dax, table excel filter, grand live, excel start today, powerpivot pivot table, excel filter number, excel filter, excel powerpivot, sum numbers, want data entry provider, pivot point, want data entry operator, download data entry estimate history file, count excel data, calculate processing order, list names want data mine email addresses, want data entry training centare addres bangladesh

About the Employer:
( 0 reviews ) United Kingdom

Project ID: #7015906

6 freelancers are bidding on average $32 for this job

abhinovpankaj

Hi, I am working as software professional in IT industry from last 4 years. I work on VB scripting along with VBA for MS office applications. Also developed various Desktop based application using Visual Studio More

$39 USD in 1 day
(12 Reviews)
4.0
sreejithakaz

I have over 9 years of experience in Excel, Access and Outlook VBA (Skills required). I have developed several Tools, Automation, Dashboard, Database CRM and Online Extraction Tools using VBA. I have done a similar More

$20 USD in 1 day
(4 Reviews)
2.7
MykolaDudych

Hello, I am interested in your offer and is ready to cooperate. I have experience in data processing and working with Excel.

$25 USD in 2 days
(1 Review)
0.8
berams

A proposal has not yet been provided

$25 USD in 1 day
(0 Reviews)
0.0
DrKTKing

Extensive experience analyzing data of all sorts (including sales/orders data) via excel, VBA programming, etc.

$55 USD in 4 days
(0 Reviews)
0.0
richybrown10

Every organization is set up with the intention of sale of either products or services. And each one of them needs to promote their product or service to survive in the market. These are times of “survival of the fitte More

$25 USD in 1 day
(0 Reviews)
0.0