Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I have table for category and sales. I want to dispaly last dat sales value and sum in table.
Category Sales Date
A 100 1jan
B 200 1jan
A 300 2jan
B 100 2jan
output - table must show
A 300 2jan
B 100 2jan
total 400
Solved! Go to Solution.
Hello, @adityavighne
According to your description, I created data to reproduce your scenario. The pbix file is attached at the end.
Mesa:
You can create two measures as follows.
SumSales =
SUMX(
SUMMARIZE(
'Table',
'Table'[Category],
"Result1",
CALCULATE(
SUM('Table'[Sales]),
FILTER(
ALLEXCEPT('Table','Table'[Category]),
[Date]=MAX('Table'[Date])
)
)
),
[Result1]
)
Latest Date = MAX('Table'[Date])
Result:
Best regards
Allan
If this post helps,then consider Accepting it as the solution to help other members find it faster.
Hello, @adityavighne
According to your description, I created data to reproduce your scenario. The pbix file is attached at the end.
Mesa:
You can create two measures as follows.
SumSales =
SUMX(
SUMMARIZE(
'Table',
'Table'[Category],
"Result1",
CALCULATE(
SUM('Table'[Sales]),
FILTER(
ALLEXCEPT('Table','Table'[Category]),
[Date]=MAX('Table'[Date])
)
)
),
[Result1]
)
Latest Date = MAX('Table'[Date])
Result:
Best regards
Allan
If this post helps,then consider Accepting it as the solution to help other members find it faster.
you can try to create a measure
Measure 2 = CALCULATE(sum('Table'[SALES]),FILTER('Table','Table'[DATE]=max('Table'[DATE])))
Proud to be a Super User!
@adityavighne , Take Category and these two measures in the visual
MAx date = max(Table[Date])
Total sales = sumx(values(Table[Category]), lastnonblankvalue(Table[Date],sum(Table[Sales])))
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |