Forum Discussion
Cumulative amount at one specific date
Hello,
I would like to calculate the cumulative amout of CA until a date that I want to be able to choose.
In my main table 'Commercial Report Daily", I have the sales amount and the Booking Date.
I want to be able to select with a segment the date until which I want the cumul.
To do that, I created a new table "Date as of" that will allow me to make my filter.
Date As Of = SUMMARIZE('Commercial Report Daily','Commercial Report Daily'[Booking Date])
In my main table, I added the variable :
Cumulative amount =
CALCULATE(
[sales amount],
FILTER(
ALLSELECTED('Commercial Report Daily'),
'Commercial Report Daily'[Booking Date] <= SELECTEDVALUE('Date As Of'[Booking Date])))
On a tab, I had the segment and selected a date + created a table with the columns : Booking Date (of my main table) and Cumulative amount. I only have the total until the selected value (same value on each Booking Date) but not the cumul day after day.
But, if I replace SELECTEDVALUE('Date As Of'[Booking Date]) by MAX('Commercial Report Daily'[Booking Date]), I have the day by day !! I don't understand where is the mistake.
To note : I have no link between my main table and the "Date as of" table.
Can anyone help me ?
1 Reply
CVERHAEGHE Bit hard to follow. However, I will say that SELECTEDVALUE only returns a value if there is a single distinct value in context. Otherwise it returns BLANK() by default unless you use the 2nd parameter to specify an alternate result. However, MAX will return the maximum value in context whether there is a single distinct value or many.
