How to get total amount based on dates criteria

[Helpful_Fun4848]

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!!

[v-luwang-msft]

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 · Accepted solution]

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"

)

[v-luwang-msft]

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]

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

[Helpful_Fun4848]

@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!

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[v-luwang-msft]

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

[Helpful_Fun4848]

@v-luwang-msft 

There is one issue:

If I create table, the total is showing the last amount only. 

Once again, thank you in advance!!

[v-luwang-msft]

Hi @Helpful_Fun4848 ，

Try to base on the measure,create a new measure:

Measure 2 = SUMX('Table','Table'[30DAY])

Best Regards

Lucien

[Helpful_Fun4848]

@v-luwang-msft 

I still have some issues:

I created the SUMX measure and if I use it on the table: 

If I use the SUMX measure on the card, the total showing different result:

Why table total different than card total?

Much thx!!

[amitchandak]

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

@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!