PowerPivot DAX (calc field & column) help

[mrzoogle]

Happy New Year everybody,

Hope you all had great fun.

I would like to get you advice on few issues I am having with powerpivot DAX please.

I am trying to achieve a calculated column which is basically a sumifs in excel language but not sure what I doing wrong.

In the attached file I have highlighted what it would look like in excel and in powerpivot it's a calculated field named "zerodays"

Second problem is a calculated field, I am trying to create 7 day moving sum and then 7 day moving average.

I am stuck at 7 day moving average as the number it's returning is wrong.

If you have some time to look into this it would be very helpful.

Thanks for your time in advance.

Kind Regards,

Z.

 

[mrzoogle]

I have also tried this formula for 7 day moving average calc field but no luck:

CALCULATE(SUM(rawdata[Cost]),DATESBETWEEN(rawdata[date],FIRSTDATE(DATEADD(rawdata[date],-6,DAY)),LASTDATE(rawdata[date])))/7

 

[Somendra Misra]

Hi M,

Can you just upload the data so that I can build the model in 2010, I am unable to access the model. This way if I am able to solve it I will post something.

Regards,

 

[mrzoogle]

Hi Somendra,

Thanks for following up on this. Please refer the attached file.

I have created a new tab named datamodal and highlighted the last 2 column which I want to add into PPT as calc field/columns.

Thanks for looking into this.

Regards,

Z.

 

[Somendra Misra]

Hi Z,

Well here are the things to try out:

For getting 0/1 i.e. column G try below formula:

=if(CALCULATE(SUM([Cost]),FILTER(Table1,Table1[client]=EARLIER(Table1[client])),filter(Table1,Table1[date]=EARLIER(Table1[date])))<300,0,1)

For getting 7 days average try below formula, well I could not incorporate your logical function of IF using ISNA & MATCH into this.

=CALCULATE(SUM([Cost]),FILTER(Table1,Table1[client]=EARLIER(Table1[client])),FILTER(Table1,Table1[date]>EARLIER(Table1[date])-7),FILTER(Table1,Table1[date]<=EARLIER([date])))/7

Regards,

 

[mrzoogle]

Hi Somendra,

Thanks for getting back on this.

These looks good, going to try these and will get back to you soon.

Thanks again for your time.

Kind Regards,

Z.

 

[mrzoogle]

Hi Somendra,

Thanks for your time on helping me out

I've tried that and it works perfectly! Haven't found any issue yet, if so I'll update.

I wasn't using filter function properly and your example have help me understand how it works.

I do have one more question though, I am trying to combine 2 table which are exported inside powerpivot via sql query.

Will it be possible for you to advice me on this please?

Thanks for your time and hope you are excited for the weekend!

Kind Regards,

Z.

 

[Somendra Misra]

Hi Z,

Well I am also learning powerpivot , so really can't help you upfront.

Regards,