Calculate sum of quantity filtered by last date in table in Power BI

[Analitika]

Hi,

I would like to ask how to calculate sum of quantity filtered by last date. I used many expressions which didn't help me. Formulae is this:

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

Measure for this.

But problem is that I am getting wrong result, it gives me 32 instead of 1. So seems it sums all values and date filter not working.

[LQuedas]

Hey @Analitika 

if I understood well you want to get the sum of a quantity value filtered by the latest date for a specific model (Pirkimai)... if I'm correct please try the following example... I'm assuming you have a dim and a fact table, if not you can use the same approach with a single table as well... so I've created a DimDate with a date column and a Sales Fact table with 4 columns, theDate that is a foreign key to the DimDate table, a Prod Name colum that will be used to filter the calculation and the Quantity Value...

With this model you can use the following simple Measure to get the the Sum of the quantity filtered by the latest date of a specific product,

CurrentSum =

var _maxDate=CALCULATE(max(FactSales[The Date]), Sales[ProductName]="Prod A")

return CALCULATE(sum(FactSales[QuantityValue]),DimDate[TheDate]=_maxDate)

Hope this example gives you an idea to solve your problem.

Cheers, LQ

[Thennarasu_R]

Hi @Analitika 
Try this Measure,
Measure=Var __Maxdate=Max(DimDate(date))
Return

kiekis_p = CALCULATE(SUM(op_detales[qty_in]), op_detales,op_detales[modul]="Pirkimai" , op_detales[Dokumento data]=__Maxdate))

Thanks,
Thennarasu

[Analitika]

Hi,

I tried but not working, with max_date I am getting result null. So something is wrong. I expected to get 1 output. 

[Thennarasu_R]

Hi @Analitika 

Try this,

kiekis_p = CALCULATE(SUM(op_detales[qty_in]),Filter( Values(op_detales[Dokumento data]),op_detales[Dokumento data]=__Maxdate),op_detales[modul]="Pirkimai"))

Thanks,
Thennarasu

[Analitika]

Just needed add && but idea is ok.