Forum Discussion
make a calculated table with distinct dates without summing up the values
Hello,
i have following source sample table (Materialtable):
| Material | TotalAmount | Date |
| ZZZ | 2000 | 06.07.2021 |
| ZZZ | 2000 | 06.07.2021 |
| ZZZ | 2000 | 06.07.2021 |
| ZZZ | 2000 | 06.07.2021 |
| ZZZ | 2000 | 07.07.2021 |
| ZZZ | 2000 | 08.07.2021 |
| ZZZ | 2000 | 09.07.2021 |
| ZZZ | 2000 | 10.07.2021 |
As you can see the total amount is always the same value. What also important to note is that in the date column only the 06.07.2021 date is existing 4 times.
So I made a calculated table where I summarized it to get only distinct dates:
| Material | TotalAmount | Date |
| ZZZ | 8000 | 06.07.2021 |
| ZZZ | 2000 | 07.07.2021 |
| ZZZ | 2000 | 08.07.2021 |
| ZZZ | 2000 | 09.07.2021 |
| ZZZ | 2000 | 10.07.2021 |
My initial intention was to calculate a cumulatie calculation, so I need a filtered table with distinct dates. But the total amount for 06.07 is wrong, it should also be 2000. In reality there are few more columns, and to calculate cumulative substracion I need the same Total Amount in every row for that material.
The normal bahaviour of DAX after grouping with summarize was to sum up the total amount of the 4 same date.
How do I get it distinct? How do I need to write the summarize function like?
Thank you very much in advance.
Best
Applicable88 , Take amount also in the group by
example new Table
Summarize(Table,[Material],[TotalAmount],[Date])
1 Reply
Applicable88 , Take amount also in the group by
example new Table
Summarize(Table,[Material],[TotalAmount],[Date])