Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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

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    
     
LocationProductCreated datePlan delivery DateTime service
ax1-2-20182-2-2018100
ay1-2-20182-2-201850
az1-2-201816-3-201875
ax1-2-20185-4-201835
ay5-2-20182-2-2018100
az7-2-20182-2-201850
ax7-2-201816-3-201875
ay8-2-20185-4-201835
bz3-2-20182-2-2018100
by3-2-20182-2-201850
bx3-2-201816-3-201875
bz3-2-20185-4-201835
bz7-2-20182-2-2018100
bx9-2-20182-2-201850
by9-2-201816-3-201875
bx10-2-20185-4-201835

 

Hope someone can help me!!

Need Help
  • Stachu's avatar
    Stachu
    8 years ago

    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

8 Replies

  • v-yuta-msft's avatar
    v-yuta-msft
    Icon for Community Support rankCommunity Support
    8 years ago

    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's avatar
      Anonymous
      Not applicable
      8 years ago

      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          
      LocationProductCreated datePlan delivery DateValue(Created date)Vlaue (Plan)Time service LocationDateWorkload_56 day         
      ax1-2-20182-2-201843132,0043133,00100  a1-2-2018225         
      ay1-2-20182-2-201843132,0043133,0050  a2-2-2018225         
      az1-2-201816-3-201843132,0043175,0075  a3-2-201875         
      ax1-2-20185-4-201843132,0043195,0035  a4-2-201875         
      ay5-2-20186-2-201843136,0043137,00100  a5-2-2018175         
      az7-2-20189-2-201843138,0043140,0050  a6-2-2018175         
      ax7-2-201816-3-201843138,0043175,0075  a7-2-2018200         
      ay8-2-20185-4-201843139,0043195,0035  a8-2-2018200         
      bz3-2-20182-2-201843134,0043133,00100  b1-2-20180         
      by3-2-20182-2-201843134,0043133,0050  b8-2-2018325         
      bx3-2-201816-3-201843134,0043175,0075  b15-2-2018470         
      bz3-2-20185-4-201843134,0043195,0035  b22-2-2018220         
      bz7-2-201818-2-201843138,0043149,00250  b1-3-2018220         
      bx9-2-20182-2-201843140,0043133,0050  b8-3-2018220         
      by9-2-201816-3-201843140,0043175,0075  b15-3-2018220         
      bx10-2-20185-4-201843141,0043195,0035  b22-3-201870         
                           
                           
       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's avatar
        Stachu
        Icon for Community Champion rankCommunity Champion
        8 years ago

        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] )