Forum Discussion
Cumulative turnover
Heya,
Could you please try this:
Cumulative Turnover (forum) = CALCULATE( SUM(Table[Turnover]),FILTER(ALLSELECTED(Table),Table[Date]<=MAX(Table[Date])))If this solved your problem then please mark it as the solution so others can see it.
Best,
Ok thank you!! Apparently the formula was conflicting with the other wrong formulas I was using. Just removing them the formula started working.
I will set your first reply as solution, but maybe you could also help me a bit more 🙂
I only want to compare this month turnover, current year vs previous year, so I modified a bit your formula:
Cumulative Turnover (forum) =
CALCULATE(
SUM(Sheet1[Turnover]),FILTER(ALLSELECTED(Sheet1),YEAR(Sheet1[Cal.date])=YEAR(TODAY()) && MONTH(Sheet1[Cal.date])=MONTH(TODAY()) && Sheet1[Cal.date]<=MAX(Sheet1[Cal.date])))And I added the same formula for previous year
Cumulative Turnover (forum) PY =
CALCULATE(
SUM(Sheet1[Turnover]),FILTER(ALLSELECTED(Sheet1),YEAR(Sheet1[Cal.date])=YEAR(TODAY())-1 && MONTH(Sheet1[Cal.date])=MONTH(TODAY()) && Sheet1[Cal.date]<=MAX(Sheet1[Cal.date])))
But in the table I have this annoying visualization:
I tried only with "Day" but it ends like that:
Do you also now how to fix this?
Thanks a lot again!!
Try the attached file, the formula is fairly simple. I also added a Date table.
Cumulative forum =
CALCULATE(SUM(Sheet1[Turnover]),ALL(Sheet1),Sheet1[Date]<=MAX(Sheet1[Date]))
Cumulative forum PY =
IF([Cumulative forum]=BLANK(),BLANK(),
CALCULATE([Cumulative forum],SAMEPERIODLASTYEAR(Sheet1[Date])))
I've attached the updated file.
If this solved your problem then please mark it as the solution so others can see it.
Best,
Hi MNedix
No my point would be to have a table like this:
Month Day Turnover Year Turnover prev year Cumulated year Cumulated prev year Aug 30 100
80 0 0 Aug 31 60 100 0 0 Sep 1 20 90 20 90 Sep 2 70 60 90 150 Sep 3 100 50 190 200 But (using your formulas with some adjustment) I only get this:
With the PY formula that is already considering all the cumulated value.
I hope it's clearer now 🙂
Thanks again!