Sum of Distinct Month Days

[Waqas1234]

Parent Account Product Nature Month Period Days Year Values
1 101 A Brown Jan 2015 1 31 2015 50
1 101 B Brown Jan 2015 1 31 2015 90
1 102 A Brown Jan 2015 1 31 2015 20
1 102 B Black Feb 2015 2 28 2015 10
2 101 A Black Feb 2015 2 28 2015 30
2 101 B Black Mar 2015 3 31 2015 40


How to Sum the No of days only once Upon each month change. I have tried using distinct formula SUMX(DISTINCT(RSF[Month]),SUM(RSF[Days])). I want to use this result further into some other calculation and expected result should be March 31 days + Feb 28 days+ Jan 31 Days = Total 90 days but I am getting instead my numbers in more than 1000 no of days. Thanks in advance.

 

[DILIPandey]

Hi Waqas1234,

After pasting your data in Excel, I am getting following view:-

[TABLE="width: 780"]
<colgroup><col span="10"></colgroup><tbody>[TR]
[TD]Parent[/TD]
[TD]Account[/TD]
[TD]Product[/TD]
[TD]Nature[/TD]
[TD]Month[/TD]
[TD]Period[/TD]
[TD]Days[/TD]
[TD]Year[/TD]
[TD]Values[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]101[/TD]
[TD]A[/TD]
[TD]Brown[/TD]
[TD]Jan[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]101[/TD]
[TD]B[/TD]
[TD]Brown[/TD]
[TD]Jan[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]90[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]102[/TD]
[TD]A[/TD]
[TD]Brown[/TD]
[TD]Jan[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]102[/TD]
[TD]B[/TD]
[TD]Black[/TD]
[TD]Feb[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]101[/TD]
[TD]A[/TD]
[TD]Black[/TD]
[TD]Feb[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]101[/TD]
[TD]B[/TD]
[TD]Black[/TD]
[TD]Mar[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]40[/TD]
[/TR]
</tbody>[/TABLE]

Now where you need this results based on what cell reference, can you give an example ?

Regards,
DILIPandey

 

[Waqas1234]

Hi

Thanks for reply

Month column is 01/01/2015 which in formatting I have changed to look like Jan 2015 and in below I think its separated when posted to excel again.

I need a formula which can give me per day revenue. To sum values its simple but sum formula can not be applied on days otherwise for each transaction date it will sum the total month days which will be wrong. I want only Jan days to come only once in denominator.

for example Jan sales = 160 (50+90+20) which should be divided by only 31 days of January and not by the sum of all 31's in the column. I am using this in another formula in pivot where I will have years at left and months at Top


1 2 3 4 5 6 7 8 9 10 11 12
2014
2015



Regards
Waqas

 

[scottsen]

You really want to have a separate Calendar table. Then it just becomes Sales Per Day :=SUM(Sales[Values]) / COUNTROWS(Calendar)

 

[cyrilbrd]

@scottsen

How do you link said calendar table with RSF? What would be the best procedure?

 

[Jsitzmann]

In your Days column use this formula:
=IF(COUNTIF($E$1:E2,E2)=1,DAY(EOMONTH(E2,0)),0)

Then you can still sum your days but it will add 0 if the count of the month is more than 1.

 

[scottsen]

Sorry for the slow reply

So, typically I try pretty hard to have a "real" Calendar table, which means it has a row for every day. Then you have lots of helper columns (year, month, Year Month, etc). It does mean that your fact data needs to be at the day level, and when pre-aggregated to some other level (month), ... you can usually get away with faking the date (Pretend Jan 2015 is Jan 1, 2015).

In your case, I would probably do that, and add a "Days in Month" column to my Calendar table.

 

[Waqas1234]

Thanks for feedback. Having a separate calendar made my life much easier and solved the problem also.