Month -> Day Allocation

[m1nkeh]

Hi,

I am allocating some targets from month level down to day, but i only want to allocate it to Working days... i have the following query, but when the monthly target is actually posted on a non-working day (i.e. 1st Feb 2014) it seems to break.. i.e. nothing is displayed at all..

CALCULATE(
SUMX (
VALUES(Dates[Date]),
CALCULATE (
CALCULATE (
SUM ( SalesTargets[Target] ),
ALL ( Dates[Date] ),
SUMMARIZE ( Dates, Dates[MonthName])
)
/ CALCULATE (
DISTINCTCOUNT ( Dates[Date] ),
ALL ( Dates[Date] ),
Dates[IsWorkingDay] = 1,
SUMMARIZE ( Dates, Dates[MonthName] )
)
)
)
,Dates[IsWorkingDay]=1
)

i can't figure out why, and without knowing why... i can't fix it

thanks

 

[scottsen]

A bit hard for me to say on this one... I don't call summarize too often, and the relationship between this month-granular sales target and day-granular date table is an interesting consideration.

Without crazy amount of thinking, I expect I would be doing stuff like...

NumDays:=CALCULATE(COUNTROWS(Dates), Dates[IsWorkingDay] = TRUE)
DailyTarget := SUM(MonthlySalesTargets[Target]) / [NumDays]
TotalTarget := SUMX(Dates, [DailyTarget])

 

[m1nkeh]

I'll give that one a go, but I think the issue will still be the same whereby I want to only display a measure on days that are non-working days and it's that bit which is falling over...

can upload another workbook

 

[m1nkeh]

the actual first stumbling block is that the NumDays measure does not consider Month, and therefore just counts a day as itself... that's why you need to 'group by' with the summarize function

 

[scottsen]

Take a look at this, and see if it gives you any ideas?
Sales/Budget: Integrating Data of Different “Grains” « PowerPivotPro

That mis-match in granularity of dates is, like I said, ... interesting.

 

[m1nkeh]

That mis-match in granularity of dates is, like I said, ... interesting.

hehe, when i sat down to do the calc i actually thought to myself... bah this can't be to hard. I'll take a look at the link, thanks!

i would imagine this grain mis-match probably isn't that uncommon. you would typically issues Sales targets at monthly, or perhaps higher level (quarter?) if you were feeling especially lazy... but want to report across months.

In order to report across months you need to have some value at the day level.