Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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,
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
Hi,
I tried but not working, with max_date I am getting result null. So something is wrong. I expected to get 1 output.
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
Just needed add && but idea is ok.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
81 | |
70 | |
62 |
User | Count |
---|---|
148 | |
116 | |
104 | |
90 | |
65 |