Forum Discussion
PowerBITestingG
Resolver I
3 years agoCalculate quantity for max date in snapshot table
I am trying to calculate the total quantity for the most recent date (12-Mar-23)
I am using:
Calculate(Quantity,snapshotdate = max(snapshot date)
I have a snapshot table like this:
| Snapshot Date | Country | Quantity | |
| 10-Mar-2023 | USA | 100 | |
| 10-Mar-2023 | USA | 100 | |
| 12-Mar-2023 | CANADA | 500 |
And if I place the measure in the matrix this is what I would exepct:
| Country | Quantity |
| USA | 0 |
| Canada | 500 |
But I am getting the latest value for USA for 10-Mar-23, instead of nothing since there is no data for the last date.
Using this formula solves the proble, but does anybody know how problematic can allselected be in this case?
test =
VAR maxx2 =
MAXX (
FILTER (
ALLSELECTED ( table ),
snapshot date
),
snapshot date
)
RETURN
CALCULATE ( quantity, Snapshot date = maxx2 )Any ideas are appreciated!
Hi,
try this:
test =VAR maxx2 =MAXX (FILTER (ALLSELECTED ( 'Table (7)' ),'Table (7)'[Snapshot Date]),'Table (7)'[Snapshot Date])RETURNif(ISBLANK(CALCULATE ( sum('Table (7)'[Quantity]),'Table (7)'[Snapshot Date] = maxx2 )),0,CALCULATE ( sum('Table (7)'[Quantity]),'Table (7)'[Snapshot Date] = maxx2 ))If I answered your question, please mark my post as solution, Appreciate your Kudos 👍
1 Reply
- DOLEARY85
Resident Rockstar
Hi,
try this:
test =VAR maxx2 =MAXX (FILTER (ALLSELECTED ( 'Table (7)' ),'Table (7)'[Snapshot Date]),'Table (7)'[Snapshot Date])RETURNif(ISBLANK(CALCULATE ( sum('Table (7)'[Quantity]),'Table (7)'[Snapshot Date] = maxx2 )),0,CALCULATE ( sum('Table (7)'[Quantity]),'Table (7)'[Snapshot Date] = maxx2 ))If I answered your question, please mark my post as solution, Appreciate your Kudos 👍