Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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!!
Solved! Go to 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
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
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)) | |||||||||||||||||||
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] )
The date came from a Dimension table from 2010 to 2099 called Date on fieldname date (Date[Date])
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
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
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]
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] )
User | Count |
---|---|
84 | |
81 | |
72 | |
72 | |
55 |
User | Count |
---|---|
108 | |
105 | |
96 | |
86 | |
68 |