Hi all,I have a column for Expected Delivery and another column showing $ amount. I would like to calculate the sum of the $ amount when Expected Delivery is between zero (today) to 30 days after, 30 days - 60 days, etc. What's the best way of doing this? Thx all!!

Problem solved: I created custom column with True or False if Expect Delivery is within 0-30 days 0-30 Column= if [ExpectDelivery] >= DateTime.LocalNow() and [ExpectDelivery] <= Date.AddDays(DateTime.LocalNow(),30) then "True" else "False" and then, I created a measure to Sum the $amount if the answer is True. CALCULATE(SUM(Table[AmtOrdered]),Table[0-30] = "True")

How to get total amount based on dates criteria

12 Replies

amitchandak
Super User
4 years ago
Helpful_Fun4848 , How want this diff, based on today?

like

new column =

var _1= datediff([Delivery Date], today(),day)

Switch( True() ,

_1< 0, " Past due",

_1<=30 , " 0 - 30 days",

_1<=60 , " 30 - 60 days",

"More than 60 day" // add others as per need

)
- Helpful_Fun4848
  Helper III
  4 years ago
  amitchandak
  
  What exactly is var _1? I'm assuming it's Variable? When I tried to create custom column, Variable.Value is the only variable available.
  
  Also, I do not see Datediff as one of the formula available.
  
  Thanks!
v-luwang-msft
Community Support
4 years ago
Hi Helpful_Fun4848 ,

Test like the below:

Base data:

Use the following dax to create measure:

30DAY = CALCULATE(SUM('Table'[amount]),FILTER(ALL('Table'),'Table'[Expected Delivery]>=NOW()&&'Table'[Expected Delivery]<=NOW()+30))
30to60 = CALCULATE(SUM('Table'[amount]),FILTER(ALL('Table'),'Table'[Expected Delivery]>=NOW()+30&&'Table'[Expected Delivery]<=NOW()+60))

Then create visual:

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien
How to get total amount based on dates criteria.pbix37 KB
- Helpful_Fun4848
  Helper III
  4 years ago
  v-luwang-msft
  
  It works! However, I have another question. There is another column for Suppliers and I tried to create Clustered bar chart to show Amount from largest to smallest per Supplier for each 0-30 days, 30-60 days, etc.
  
  When I select the measure and the supplier onto the chart, the chart is showing incorrect result. For example, the total amount for 0-30 days = $1.2M, the chart shows the same amount of $1.2M for every Suppliers.
  
  If I create Table, it'll show the result like the table on your post above where every Suppliers have $1.2M in the next 30 days.
  
  Much appreciate it if you could tell me the solution!
  - v-luwang-msft
    Community Support
    4 years ago
    Hi Helpful_Fun4848 ,
    
    Try the following measure ,a little adjust:
    
    30DAY = CALCULATE ( SUM ( 'Table'[amount] ), FILTER ( ALL ( 'Table' ), 'Table'[Expected Delivery] >= NOW () && 'Table'[Expected Delivery] <= NOW () + 30 && 'Table'[Supplier] = MAX ( 'Table'[Supplier] ) ) )
    30to60 = CALCULATE ( SUM ( 'Table'[amount] ), FILTER ( ALL ( 'Table' ), 'Table'[Expected Delivery] >= NOW () + 30 && 'Table'[Expected Delivery] <= NOW () + 60 && 'Table'[Supplier] = MAX ( 'Table'[Supplier] ) ) )
    
    Did I answer your question? Mark my post as a solution!
    
    Best Regards
    
    Lucien
v-luwang-msft
Community Support
4 years ago
Hi Helpful_Fun4848 ,

In my sample ,it with the same value .But my sampe not with the column supplier.

And could you pls share your pbix file through a public link?Remember to remove confidential data.

Best Regards

Lucien
v-luwang-msft
Community Support
4 years ago
Hi Helpful_Fun4848 ，

Has your problem been solved, if so, please consider Accept a correct reply as the solution or share your own solution to help others find it.

Best Regards
Lucien
- Helpful_Fun4848
  Helper III
  4 years ago
  Problem solved: I created custom column with True or False if Expect Delivery is within 0-30 days
  
  0-30 Column
  = if [ExpectDelivery] >= DateTime.LocalNow() and [ExpectDelivery] <= Date.AddDays(DateTime.LocalNow(),30) then "True" else "False"
  
  and then, I created a measure to Sum the $amount if the answer is True.
  
  CALCULATE(
  SUM(Table[AmtOrdered]),
  Table[0-30] = "True"
  )