Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
73 | |
65 |