Difference between measure and total

[NKD]

Hello,

I'm having a weird result and got no idea why.

I have a sales table with a calendar date, a calendar table with 1-* simple relationship.

These are the fields:

Chiffre d'Affaires = sum(ventes_restauration[recette_totale_ht])

Chiffre d'Affaires N-1 = CALCULATE([Chiffre d'Affaires], SAMEPERIODLASTYEAR(calendrier[Date]))

And their cumulated version:

C.A Cumulé = TOTALYTD([Chiffre d'Affaires], calendrier[Date])

C.A Cumulé N-1 = CALCULATE([C.A Cumulé], SAMEPERIODLASTYEAR(calendrier[Date]))

 Here is the result when setting up the fields in a table:

There is a problem in the N-1 calculation.

The measure says 12.704.500 € but the automatic total from PBI Desktop says 12.901.576 € which corresponds to the running sum including whole august.

Any idea how to fix the formulas ?

The idea is that the running sum should go up to today's date.

Thanks a lot.

[danextian · Accepted solution]

Hi @NKD 

Assuming today's date doesnt' necessarily mean today but the latest date with data in your semantic model, try this:

Cumulative LY with a stop = 
VAR _lastdate =
    CALCULATE (
        LASTNONBLANK ( CalendarTable[Date], [Sales Amount] ),
        ALLSELECTED ( CalendarTable )
    )
RETURN
    CALCULATE (
        CALCULATE ( [Cumulative CY], SAMEPERIODLASTYEAR ( CalendarTable[Date] ) ),
        KEEPFILTERS ( CalendarTable[Date] <= _lastdate )
    )

Please see the attached pbix.

[NKD]

Best solutions, thanks !

Small side remark, when plotting cumulative sales VS LY sales:

We see that the values of C.A Cumulé keep on going as a straight line in august 25 (checked the data, there is nothing)

Fixed the cumulative of last year, but it draws funny.

[Abhilash_P]

Hi @NKD,

The issue you’re seeing happens because your "last year" cumulative total is adding up sales for the whole year up to the end of August, instead of stopping at today's date but last year.

When you use SAMEPERIODLASTYEAR, it shifts the date range to the previous year but doesn’t automatically stop the running total at the equivalent day of the year like today. So, your total includes extra days after today's date from last year, which is why it’s bigger than expected.

To fix this, you need count sales in last year’s data at the same day and month as today, Basically, don’t count beyond today’s date, but a year ago.

You can do this by creating a measure that finds today’s date, then creates a corresponding date one year earlier, and limits the running total up to that date only.

That way, your cumulative total for last year stops exactly where the current year’s total stops, and the totals will match properly.

Here are the formulas you can use to fix your issue in Power BI:

1. Regular "Chiffre d'Affaires" measure stays the same = 

   Chiffre d'Affaires = SUM(ventes_restauration[recette_totale_ht])

1. Last Year "Chiffre d'Affaires" measure stays the same = 
   Chiffre d'Affaires N-1 = CALCULATE([Chiffre d'Affaires], SAMEPERIODLASTYEAR(calendrier[Date]))
   
   

2. Cumulative total for the current year up to today:
   C.A Cumulé = TOTALYTD([Chiffre d'Affaires], calendrier[Date], TODAY())
   

3. Cumulative total for last year up to the equivalent of today's date last year (this is the key fix):

   C.A Cumulé N-1 = VAR TodayDate = TODAY() VAR TodayLastYear = DATE(YEAR(TodayDate) - 1, MONTH(TodayDate), DAY(TodayDate)) RETURN CALCULATE( TOTALYTD( [Chiffre d'Affaires], calendrier[Date], TodayLastYear ), FILTER( ALL(calendrier), calendrier[Date] <= TodayLastYear ) )

This last measure ensures your cumulative total for last year stops exactly at the same day and month as today’s date but in the previous year. It will fix the discrepancy you were seeing with the totals.

[ChielFaber]

This is a fairly common problem. Check out the usefull video:

https://www.youtube.com/watch?v=yw0QHu9V4UQ