Forum Discussion

HitcH's avatar
HitcH
Helper II
1 year ago
Solved

Cumulative turnover

Hello everyone I know that this topic has been discussed many times but I couldn't solve it. This is my database:   I want to see cumulative turnover for current month - current year. I tr...
Need Help
Tips and Tricks
  • MNedix's avatar
    MNedix
    1 year ago

    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,

MNedix's avatar
MNedix
Solution Sage
1 year ago

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,

  • HitcH's avatar
    HitcH
    Helper II
    1 year ago

    Hi MNedix 

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

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

    Still, the dates are formatted as date:

     

    • MNedix's avatar
      MNedix
      Solution Sage
      1 year ago

      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

      • HitcH's avatar
        HitcH
        Helper II
        1 year ago

        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!!