Tabular DAX Apportion / Scope Budget Month to Day

Question

I've a simple PowerPivot workbook with some SalesTargets in one table, these are by month.. typically the 1st of each month, and a date dimension.

I have written (through much pain) a DAX query to apportion the monthly target that we recieve down to days for the purposes of inter-month reporting (otherwise there would be no target data at that grain)..

I'm finding however that there is something up with my query that i'm not sure how to fix in that if i attempt to only show an apportioned target value at day level where that day is a working day things go a bit wrong... basically nothing is displayed at any hierarchy level, and i don't know why :(

without knowing why, i can't fix it... here is my current code:

Measure :=
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
)

The code is attempting to sum the sales targets at a month level and then divide that number the sum of dates where IsWorkingDay = 1

This process is carried out for all Dates[Date] (lowest grain) of the date table, and then i attempt to filter on the dates where IsWorkingDay = 1 again..

If i remove the Working day logic from both areas in the code, everything works fine.. and rolls up across months, quarters and years without a problem..

I am sure that if i understood what the issue was here, I would be able to resolve it... i just cant see it though.

Thanks

hi, i could provide some sample data... i'll try to upload a workbook to OneDrive or something when i have a moment, however i have actually now solved this issue through breaking up the query into seperate portions and generally engaging my brain ;) — m1nkeh
BGM - if you get a chance, please still upload the data- I would like to try your solution :) — Petr Havlik

m1nkeh m1nkeh · Accepted Answer · 2014-08-19T11:20:00

Here is what i went with in the end:

SalesTarget :=
CALCULATE (
    SUMX (
        VALUES ( Dates[Date] ),
        CALCULATE (
            CALCULATE (
                [Sum of Target],
                ALL ( 'Dates' ),
                SUMMARIZE ( Dates, Dates[MonthName], Dates[Year] )
            )
                / CALCULATE (
                    [Sum of IsWorkingDay],
                    ALL ( 'Dates' ),
                    SUMMARIZE ( 'Dates', 'Dates'[MonthName] )
                )
        )
    ),
    Dates[IsWorkingDay] = 1
)

notice that the only real difference here is the removal of the distinct count of days where IsWorkingDay = 1

I noticed that this wasn't giving me the right answer when i broke the calc out into smaller sets, and therefore changed it something else after a bit of playing about..

In my date table, IsWorkingDay is a boolean field of 1 or 0, but you could also do this with something like:

WorkingDays :=
CALCULATE (
    COUNTROWS ( 'Dates' ),
    ALL ( 'Dates' ),
    SUMMARIZE ( 'Dates', 'Dates'[MonthName] ),
    'Dates'[IsWorkingDay] = "Working Day"
)

Hope this post helps someone :)

Tabular DAX Apportion / Scope Budget Month to Day

1 Answers