Forum Discussion

NKD's avatar
NKD
Frequent Visitor
1 year ago
Solved

Difference between measure and total

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(...
Need Help
  • danextian's avatar
    danextian
    1 year ago

    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.

     

danextian's avatar
danextian
Icon for Super User rankSuper User
1 year ago

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's avatar
    NKD
    Frequent Visitor
    1 year ago

    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.