Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hi,
I need a measure that calculates the sum of quantities on the latest available date. This is my example table:
Item1 | Location1 | QTY1 | Date1 |
A | X | 1 | 2.2.2023 |
C | X | 1 | 2.2.2023 |
C | Y | 1 | 2.2.2023 |
B | Z | 1 | 31.1.2023 |
A | Y | 2 | 31.1.2023 |
A | X | 2 | 31.1.2023 |
C | Z | 1 | 30.1.2023 |
C | Z | 1 | 30.1.2023 |
A | X | 3 | 30.1.2023 |
A | Y | 3 | 29.1.2023 |
B | Y | 1 | 29.1.2023 |
B | Z | 1 | 29.1.2023 |
C | X | 2 | 29.1.2023 |
So the desired output should be:
For example, if I want to see quantities per item for the entire year:
2023 | |
A | 1 |
B | 0 |
C | 2 |
Expected output for all items per date:
29.1.2023 | 30.1.2023 | 31.1.2023 | 1.2.2023 | 2.2.2023 | |
A | 3 | 3 | 4 | 0 | 1 |
B | 2 | 0 | 1 | 0 | 0 |
C | 2 | 2 | 0 | 0 | 2 |
Or if only X location is in the filters per date:
29.1.2023 | 30.1.2023 | 31.1.2023 | 1.2.2023 | 2.2.2023 | |
A | 0 | 3 | 2 | 0 | 1 |
B | 0 | 0 | 0 | 0 | 0 |
C | 2 | 0 | 0 | 0 | 1 |
Or if I only look at Z location per Month:
JAN | FEB | |
Z | 1 | 0 |
I have tried using LASTNONBLANK() but it gives me the wrong values since my raw table doesn't include rows where the value is 0.
this was my try:
Hi @mihzuk ,
Based on the sample and description you provided, You may also consider try the following steps:
1. Please try code as below to Create Calculated column.
Index = RANKX('Table','Table'[Date1],,DESC,Dense)
MonthNumber = MONTH('Table'[Date1])
2. Use the following code to create measures.
Latest QTY =
VAR _Item =
CALCULATE ( MAX ( 'Table'[Item1] ), FILTER ( 'Table', 'Table'[Index] = 1 ) )
RETURN
IF (
_Item IN { "A", "B", "C" },
CALCULATE ( SUM ( 'Table'[QTY1] ), FILTER ( 'Table', 'Table'[Index] = 1 ) ),
0
)
QTY by Item = CALCULATE(SUM('Table'[QTY1]),FILTER('Table','Table'[Date1]))
QTY by Month = CALCULATE(SUM('Table'[QTY1]),FILTER('Table','Table'[MonthNumber]))
Result is as below.
Best Regards,
Yulia Yan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@mihzuk
Check the attached PBI file for the meaure and the data model:
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
That works great until I put Item as a filter. Then it brings me back to latest QTY that I have in my table.
Example
Latest value should be 0 since there is no QTY on 02.02.2023.
Could you look into that?
Thank you!
you checked my solution
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
142 | |
80 | |
63 | |
52 | |
48 |
User | Count |
---|---|
211 | |
87 | |
80 | |
69 | |
60 |