datesinperiod with two different date fields (SUMIFS Exclel) not working

[Anonymous]

Hello there,

I'm trying to convert a working Excel SUMIFS formula in to DAX.

Am not very experienced in DAX user.

What i'm trying to do is calculate a sum for a throughput time in seconds for a combination of filters on two diffenrent date fields.

In my Reservations table i have a location and the value Time service (minutes) which indicates the time needed for a certain service.

I now want to calculate how much work i can expect for the next 56 days for a locaction and product, or overall.

I want to calculate the amount of time [Time service] for [Created date] equal or earlier than any chosen date and for [Plan delivery date] in the next 56 days from the chosen date.

Using a DimDate and DATESINPERIOD finctios but can't seem to make it work. The results are missing dates that are not in the Reservations table.

CALCULATE(SUM(Reservations[Time service]),DATESINPERIOD(Date[Date],LASTDATE(Reservations[Creatted date]),-1,year),ALL(Date),DATESINPERIOD(Date[Date],LASTDATE(Reservations[Plan delivery date]),56,day))

Here a sample of the data table [Reservations].

Example
Location	Product	Created date	Plan delivery Date	Time service
a	x	1-2-2018	2-2-2018	100
a	y	1-2-2018	2-2-2018	50
a	z	1-2-2018	16-3-2018	75
a	x	1-2-2018	5-4-2018	35
a	y	5-2-2018	2-2-2018	100
a	z	7-2-2018	2-2-2018	50
a	x	7-2-2018	16-3-2018	75
a	y	8-2-2018	5-4-2018	35
b	z	3-2-2018	2-2-2018	100
b	y	3-2-2018	2-2-2018	50
b	x	3-2-2018	16-3-2018	75
b	z	3-2-2018	5-4-2018	35
b	z	7-2-2018	2-2-2018	100
b	x	9-2-2018	2-2-2018	50
b	y	9-2-2018	16-3-2018	75
b	x	10-2-2018	5-4-2018	35

Hope someone can help me!!

[v-yuta-msft]

Hi Ruud_K ,

"

I now want to calculate how much work i can expect for the next 56 days for a locaction and product, or overall.

I want to calculate the amount of time [Time service] for [Created date] equal or earlier than any chosen date and for [Plan delivery date] in the next 56 days from the chosen date.

"

<--- Which relationship between date table and reservation table, could you clarify more details about your expected result?

Regards,

Jimmy Tao

[Anonymous]

Hi Jimmy,

Its actually very simple.

I have the two date fields linked to my Date table.

The link for [Plan delivery date] is active. The other inactive since i can only choose one active realitionship. 

The expected results and the excel formulas are as followed:

Example
Table Reservations								Expected result
Location	Product	Created date	Plan delivery Date	Value(Created date)	Vlaue (Plan)	Time service		Location	Date	Workload_56 day
a	x	1-2-2018	2-2-2018	43132,00	43133,00	100			a	1-2-2018	225
a	y	1-2-2018	2-2-2018	43132,00	43133,00	50			a	2-2-2018	225
a	z	1-2-2018	16-3-2018	43132,00	43175,00	75			a	3-2-2018	75
a	x	1-2-2018	5-4-2018	43132,00	43195,00	35			a	4-2-2018	75
a	y	5-2-2018	6-2-2018	43136,00	43137,00	100			a	5-2-2018	175
a	z	7-2-2018	9-2-2018	43138,00	43140,00	50			a	6-2-2018	175
a	x	7-2-2018	16-3-2018	43138,00	43175,00	75			a	7-2-2018	200
a	y	8-2-2018	5-4-2018	43139,00	43195,00	35			a	8-2-2018	200
b	z	3-2-2018	2-2-2018	43134,00	43133,00	100			b	1-2-2018	0
b	y	3-2-2018	2-2-2018	43134,00	43133,00	50			b	8-2-2018	325
b	x	3-2-2018	16-3-2018	43134,00	43175,00	75			b	15-2-2018	470
b	z	3-2-2018	5-4-2018	43134,00	43195,00	35			b	22-2-2018	220
b	z	7-2-2018	18-2-2018	43138,00	43149,00	250			b	1-3-2018	220
b	x	9-2-2018	2-2-2018	43140,00	43133,00	50			b	8-3-2018	220
b	y	9-2-2018	16-3-2018	43140,00	43175,00	75			b	15-3-2018	220
b	x	10-2-2018	5-4-2018	43141,00	43195,00	35			b	22-3-2018	70
	SUMIFS		=SOMMEN.ALS($K$11:$K$26;$H$11:$H$26;">="&O11;$H$11:$H$26;"<"&O11+56;$G$11:$G$26;"<="&O11;$E$11:$E$26;N11)
	DAX		CALCULATE(SUM(Reservations[Time service]),DATESINPERIOD(Date[Date],LASTDATE(Reservations[Creatted date]),-1,year),ALL(Date),DATESINPERIOD(Date[Date],LASTDATE(Reservations[Plan delivery date]),56,day))

[Stachu]

I want to calculate the amount of time [Time service] for [Created date] equal or earlier than any chosen date and for [Plan delivery date] in the next 56 days from the chosen date.

this should work after claryfing where 'any chosen date' is coming from, now it's hardcoded to 2018-03-15 in SelectedDate variable. Is it supposed to come from Plan Delivery Date? the dates in your Excel example are not matching with it

Measure =
VAR SelectedDate =
    DATE ( 2018, 3, 15 )
VAR Workload =
    FILTER (
        'Table',
        'Table'[Plan delivery Date]
            < SelectedDate + 56
            && 'Table'[Plan delivery Date] >= SelectedDate
            && 'Table'[Created date] <= SelectedDate
    )
RETURN
    SUMX ( Workload, [Time service] )

[Anonymous]

The date came from a Dimension table from 2010 to 2099 called Date on fieldname date (Date[Date])

[Anonymous]

Thans for the input.

Formula is working but does not give the right results.

To clarify.

I would like to make a vusual where i can show the known planned workload over an time period of say 16 months.

This means the sum of the values [Time_service] for all the [Created_Date] equal or older than 2016;2;16 and all the [Plan_Delivery_Date] equal or 56 days after 2016;2;16.

I have used the following Formula:

Measure 3 = VAR SelectedDate =
DATE (2016;2;16)
VAR Workload =
    FILTER (
        'Reservations';
        'Reservations'[Plan_Delivery_Date]
            < SelectedDate + 56
            && 'Reservations'[Plan_Delivery_Date] >= SelectedDate
            && 'Reservations'[Created_Date] <= SelectedDate     )
RETURN
    SUMX ( Workload; [Time_service] )

My Datamodel is as follows:

The results from the formula do not match the expected values.

What do i need to alter in the DAX formula.

PS. I would prefer a formula in which the Dat_Dim is used in stead of a fixed date. Since i want a visualisation on more than 1 date

[Stachu · Accepted solution]

try  this

Measure 3 = 
VAR SelectedDate = SELECTEDVALUE(Date_Dim[Date])
VAR Workload =
    FILTER (
        'Reservations',
        'Reservations'[Plan_Delivery_Date]
            < SelectedDate + 56
            && 'Reservations'[Plan_Delivery_Date] >= SelectedDate
            && 'Reservations'[Created_Date] <= SelectedDate
    )
RETURN
    SUMX ( Workload, [Time_service] )

it works if both of the date joins between Reservations and Dim_Date are inactive

[Anonymous]

Hey @Stachu

Works perfectly. Thanks for this.

Could i bother anyone for a extra question on this problem.

Is it possible to get the values but not for 56 days in betweek but for 40 workdays.

The date table has a workday indicator (J/N)  named [Workday_ind] and a Workdat value (1 or 0) named [Workday_value]

[Stachu]

try this, you may play with >=/> to get what you need exactly

Measure 3 = 
VAR SelectedDate = SELECTEDVALUE(Date_Dim[Date])
VAR OnlyWorking = FILTER(ALL(Date_Dim[Date], Date_Dim[Workday_value]), Date_Dim[Workday_value]<>0 && Date_Dim[Date]>SelectedDate)
VAR RollingWD = ADDCOLUMNS(OnlyWorking,"RollingWD",RANKX(OnlyWorking, [Date], ,ASC))
VAR NextDate = CALCULATE(MAX(Date_Dim[Date]),FILTER(RollingWD,[RollingWD]=40))
VAR Workload =
    FILTER (
        'Reservations',
        'Reservations'[Plan_Delivery_Date]
            <= NextDate
            && 'Reservations'[Plan_Delivery_Date] >= SelectedDate
            && 'Reservations'[Created_Date] <= SelectedDate
    )
RETURN
    SUMX ( Workload, [Time_service] )

EDIT
this may work even better:

Measure 3 = 
VAR SelectedDate = SELECTEDVALUE(Date_Dim[Date])
VAR OnlyWorking = FILTER(ALL(Date_Dim[Date], Date_Dim[Workday_value]), Date_Dim[Workday_value]<>0 && Date_Dim[Date]>SelectedDate)
VAR Top40WD = TOPN(40,OnlyWorking,[Date],ASC)
VAR NextDate = CALCULATE(MAX(Date_Dim[Date]),Top40WD)
VAR Workload =
    FILTER (
        'Reservations',
        'Reservations'[Plan_Delivery_Date]
            < NextDate
            && 'Reservations'[Plan_Delivery_Date] >= SelectedDate
            && 'Reservations'[Created_Date] <= SelectedDate
    )
RETURN
    SUMX ( Workload, [Time_service] )