Sum values in column by condition in Power Bi

[Analitika]

Hi,

I have columns in table, quantity and date. I need to calculate sum values based on last date of that object. For example:

First column quantity, second -  date

Based on this example, result must be 1. But I am getting 32, it is wrong. My measure:

kiekis_test1 = 
CALCULATE(sum(op_detales[qty_in]),FILTER(op_detales,op_detales[Dokumento data]=[_m_date_A] && op_detales[modul]="Pirkimai"))

So how to fix my measure?

[Mikelytics]

HI @Analitika 

This is really a normal measure filter.

1 Idea) What happens if you take all thgese filters out. Can you please to that?

2 Idea) Please creat a normal SUM Meausre and filter on the 26/8/2022

Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.

[Thennarasu_R]

Hi @Analitika 
Try this Measure

kiekis_test1 = 
var __LastDate =Max(op_detales[Dokumento data])
RETURN
CALCULATE(
   count(op_detales[qty_in]),
   FILTER(Values(op_detales),op_detales[Dokumento data]=__LastDate && op_detales[modul]="Pirkimai")
)

Thanks ,
Thennarasu 

[Analitika]

Result is empty.

[Mikelytics]

Hi @Analitika 

Can you please try:

kiekis_test1 = 
var __LastDate = LASTDATE(op_detales[Dokumento data])
RETURN
CALCULATE(
   sum(op_detales[qty_in]),
   FILTER(op_detales,op_detales[Dokumento data]=__LastDate && op_detales[modul]="Pirkimai")
)

Can you please show the results in a visual if it does not work? By that I can better see how you use the measure.

Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.

[Analitika]

Now I am getting null result like this:

[Mikelytics]

Are there any filters applied to the visual? Can you maybe provide sample data?

[Analitika]

Nope, I don't see filters

[Mikelytics]

only for testig. What happens when you take out the second gilter in your measure?

kiekis_test1 = 
var __LastDate = LASTDATE(op_detales[Dokumento data])
RETURN
CALCULATE(
   sum(op_detales[qty_in]),
   FILTER(op_detales,op_detales[Dokumento data]=__LastDate)
)

or if you use it like this

kiekis_test1 = 
var __LastDate = LASTDATE(op_detales[Dokumento data])
RETURN
CALCULATE(
   sum(op_detales[qty_in]),
   op_detales[Dokumento data]=__LastDate
)

Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.

[Analitika]

It gives me 0 all time.

[Mikelytics]

@Analitika 

Can you also please show the pop-up which comes when you click here?

Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.

[Analitika]

[Mikelytics]

@Analitika you see, there are filters on the visual. Are you 100% sure that these filters do not filter out the data you want to see?

Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.

[Analitika]

[Analitika]

Yeah I am sure. 

kiekis_test1 = 
var __LastDate = LASTDATE(op_detales[Dokumento data])
RETURN
CALCULATE(
   sum(op_detales[qty_in]),
   FILTER(op_detales,op_detales[Dokumento data]=__LastDate)
)

 

Zero values, seems filtering in measure not working.

Because without filtering it sums all values.

[Mikelytics]

hm @Analitika 

Can you try:

kiekis_test1 = 

CALCULATE(
   sum(op_detales[qty_in]),
   LASTDATE(op_detales[Dokumento data])
)

Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.

[Analitika]

Yeah I am  getting 0

[Mikelytics]

Okay, since the data is coming but the value is zero it ahst  to be one fo these filters

Beacuse you measure identifies the right data but the result is zero. This means there further criteria which filter out further rows. It has to be one of those.

Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.

[Analitika]

I checked that not affects calculations, I think filter in measure not works well.

[Mikelytics]

HI @Analitika 

This is really a normal measure filter Can you please try the following twpo options.

1) Take out all the filter on the visual and look for the result

2) Create a normal SUM measure and filter on the date which you got from last date

Can you please tell what happens?

Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.

[Analitika]

Filtering works well with other columns, with date it doesn't work. So seems problem is with date.