Search code examples
sumdaxpowerbi-desktop

SUM over column without using SUMMARIZE function

I have created a DAX code using SAMEPERIODLASTYEAR which returns the correct value within a table but on a card visual and in the grand total it shows an incorrect value.

DAX:

ROS£PreviousYear = 
    IF (
    ISFILTERED('Calendar'[weekNumWeekCommence]) || ISFILTERED('Calendar'[Calendar Financial Year]),
    CALCULATE ( 
        [ROS £ 2],
        SAMEPERIODLASTYEAR('Calendar'[Calendar Date])
    ),
    0
)

Output:

enter image description here

Is there a way I can get the correct sum for that previous year column without using the SUMMARIZE function?

Solution

  • Normally it would simply work for the Total but it seems other things are at play. As a test, try:

    ROS£PreviousYear = 
  IF (
    ISFILTERED('Calendar'[weekNumWeekCommence]) || 
    ISFILTERED('Calendar'[Calendar Financial Year]),
    
    SUMX(
      VALUES('Campaign Overview'[day_id]),
      CALCULATE ( 
        [ROS £ 2],
        SAMEPERIODLASTYEAR('Calendar'[Calendar Date])
      )
    ),
    
    0
  )