Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
HitcH
Helper II
Helper II

Cumulative turnover

Hello everyone

I know that this topic has been discussed many times but I couldn't solve it.

This is my database:

HitcH_0-1726133537507.png

 

I want to see cumulative turnover for current month - current year.

I tried in many ways:

 

Cumulative Turnover1 = 
CALCULATE(
    SUM(Sheet1[Turnover]),
    FILTER(
        ALL('Sheet1'),
        DAY(Sheet1[Cal.date]) <= DAY(TODAY()) && MONTH(Sheet1[Cal.date]) = MONTH(TODAY()) && YEAR(Sheet1[Cal.date]) = YEAR(today())
))
Cumulative Turnover2 = 
TOTALMTD(
SUM(Sheet1[Turnover]),'Controlling BI'[Calendar day.Calendar day Level 01],MONTH('Controlling BI'[Calendar day.Calendar day Level 01])=MONTH(TODAY()))
Cumulative Turnover3 = CALCULATE(SUM(Sheet1[Turnover]), DATESBETWEEN(Sheet1[Cal.date],DATE(YEAR(TODAY()),MONTH(TODAY()),1),DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))))

 

 

First one is calculating the value correctly but it is showing it on every day, second one is not cumulating anything, third one is only considering turnover day by day, not cumulated:

HitcH_1-1726133766044.png

Does anybody knows how to solve this? My CEO expect this from me 😞

1 ACCEPTED SOLUTION
MNedix
Super User
Super User

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,



If the post helped then please give it a Kudos and mark it as the solution so others can see it.
Cheers,

View solution in original post

6 REPLIES 6
MNedix
Super User
Super User

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,



If the post helped then please give it a Kudos and mark it as the solution so others can see it.
Cheers,

Hi @MNedix 

I think that the formula is ok but the date is not:

HitcH_0-1726146293139.png

For some reason PBI is considering June 2023 > of today.

Still, the dates are formatted as date:

HitcH_1-1726146372924.png

 

I don't really understand the problem. I expanded the dates and turnover values and the formula works just fine (see attached file). Either you have something else in your data or you'll probably need to rephrase the issue.

 

If this solved your problem then please mark it as the solution so others can see it.

 

Best,

 

---

PS: please keep in mind that I did not use a Date table, I simply worked with unique values



If the post helped then please give it a Kudos and mark it as the solution so others can see it.
Cheers,

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:

HitcH_0-1726156740710.png

I tried only with "Day" but it ends like that:

HitcH_1-1726156783503.png

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,



If the post helped then please give it a Kudos and mark it as the solution so others can see it.
Cheers,

Hi @MNedix 

No my point would be to have a table like this:

MonthDayTurnover YearTurnover prev yearCumulated yearCumulated prev year
Aug30

100

8000
Aug316010000
Sep120902090
Sep2706090150
Sep310050190200

 

But (using your formulas with some adjustment) I only get this:

HitcH_0-1726216059316.png

With the PY formula that is already considering all the cumulated value.

I hope it's clearer now 🙂

Thanks again!

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.