Summarize Measire

[joshua1990]

Hello everybody!

Firts of all, please do not just add some "maybe" usefull links.

I am really looking for a solution and an explanation.

Somehow I need to accumulate a weekly budget to a daily level.

So, I have a Weekly Budget of 500k. Divided by the number of working days I will have a daily budget of 100k.

That means on a Tuesday I have a Buget Target of 200k, right?

Unfortunately this is not so easy to calculate.

The Budget table is linked throught the YearWeek key with the calendar table and through the Article with our item master.

The structure is like:

YearWeek-Budget-Article.

I have divided this Budget by the amount of working days per Week with the following measure:

# Working Days per Week = 
CALCULATE (
    SUM ( 'Calendar'[IsWorkingDay] ),
    ALL ( 'Calendar' ),
    VALUES ( 'Calendar'[Fiscal Week] ),
    VALUES ( 'Calendar'[Fiscal Year] )
)

Budget per Day = DIVIDE([Budget],[# Working Days per Week])

Now if I use a measure like:

Test = 
VAR LastVisibleDate =
    MAX ( 'Calendar'[Date] )
VAR FirstVisibleDate =
    MIN ( 'Calendar'[Date] )
VAR LastDateWithSales =
    CALCULATE (
        MAX ( 'Calendar'[Date] ),
        REMOVEFILTERS ()   -- Use ALL ( Sales ) if REMOVEFILTERS () and ALL () 
                           -- are not available
    )
VAR Result =
    IF (
        FirstVisibleDate <= LastDateWithSales,
        CALCULATE (
            [Starts Target Phasing],
            'Calendar'[Date] <= LastVisibleDate 
        )
    )
RETURN
    Result

It is not working.

As you can see in the last column, there is just the accumulation on once of the result of the second measure, not per Day.

I need this accumulated for every working day.

[amitchandak]

@joshua1990 , refer if logic in this file can help

https://www.dropbox.com/s/fnq82ksdzk1lqs3/Target_allocation_daily.pbix?dl=0

[joshua1990]

@amitchandak Thank you so much for your support!

Since my tables are linked, do I need a Summarize and Crossjoin?

Daily Target =
SUMMARIZE (
    FILTER (
        CROSSJOIN ( 'Date Table', 'Monthly Target' ),
        'Date Table'[End of Month] = 'Monthly Target'[Month]
    ),
    'Monthly Target'[Manufacturer],
    'Monthly Target'[Seller],
    'Monthly Target'[Month],
    'Date Table'[Date],
    "Daily Target", CALCULATE (
        DIVIDE (
            MAX ( 'Monthly Target'[Month Target] ),
            MAXX (
                'Date Table',
                'Date Table'[Working day] * 'Date Table'[Working days in Month]
            )
        )
    )
)

If so, this approach is not working:

Daily Target =
CALCULATE (
        DIVIDE (
            MAX ( 'Monthly Target'[Month Target] ),
            MAXX (
                'Date Table',
                'Date Table'[Working day] * 'Date Table'[Working days in Month]
            )
        )
    )

[Anonymous]

// Only works when Calendar
// is a date table in the model
// marked as a Date table.

// Hidden measure
[_Budget] = SUM( Budget[Amount] )

[Budget Per Day] =
var __oneDateVisible =
	HASONEVALUE( Calendar[Date] )
var __workingDayVisible =
	SELECTEDVALUE(
		// This field should be 
		// of type bool.
		Calendar[IsWorkingDay],
		FALSE()
	)
var __shouldCalc =
	__oneDateVisible
	&& __workingDayVisible
RETURN
if( __shouldCalc,
	,
	// Get the value of the budget
	// for the currently visible week.
	var __weeklyBudget = [_Budget]
	// Get the number of working days
	// in the currently visible week;
	// FiscalWeekId must be a unique
	// identifier of a fiscal week
	// across all fiscal years.
	var __workingDays = 
		CALCULATE(
			SUM( 1 * Calendar[IsWorkingDay] ),
			VALUES( Calendar[FiscalWeekID] ),
			ALL( 'Calendar' )
		)
	return
		__weeklyBudget / __workingDays
)

// Not sure exactly what it is you want
// but this is a Week-To-Date measure.
[WTD Budget] =
var __onlyOneWeekVisible =
	HASONEVALUE( Calendar[FiscalWeekId] )
return
if( __onlyOneWeekVisible,
	var __lastVisibleDate = LASTDATE( Calendar[Date] )
	var __firstVisibleDate = FIRSTDATE( Calendar[Date] )
	var __lastDateWithSales =
		CALCULATE(
			MAX( Sales[Date] ),
			ALL( Sales )
		)
	var __wtdBudget =
		if(
			__firstVisibleDate <= __lastDateWithSales,
			CALCULATE(
				SUMX(
					'Calendar',
					[Budget Per Day]
				),
				VALUES( Calendar[FiscalWeekID] ),
				Calendar[Date] <= __lastVisibleDate
			)
		)
	return
		__wtdBudget
)