Forum Discussion

ajisharavind_99's avatar
ajisharavind_99
Icon for Advocate I rankAdvocate I
3 years ago
Solved

Avoid filter other Tables

Hi, When I calculate cumulative progress, it is 0.25 percent greater than total due to the relationship between the date dimension table and the other two tables. The outcome is satisfactory when I ...
DAX
Help Request
  • johnt75's avatar
    johnt75
    3 years ago

    Try

    Overall_Actual_Cumulative_Prgrs =
VAR MaxDate =
    CALCULATE ( MAX ( 'DIM Calendar'[Date] ), 'DIM Calendar'[Date] <= TODAY () )
RETURN
    CALCULATE (
        [Overall_Total Actual Prgrs],
        ALLSELECTED ( 'DIM Calendar' ),
        'DIM Calendar'[Date] <= MaxDate
    )
johnt75's avatar
johnt75
Icon for Super User rankSuper User
3 years ago

Change the relationships from the date table to all the other tables to be one-to-many single-direction relationships.

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    3 years ago

    Try

    Overall_Actual_Cumulative_Prgrs =
VAR MaxDate =
    CALCULATE ( MAX ( 'DIM Calendar'[Date] ), 'DIM Calendar'[Date] <= TODAY () )
RETURN
    CALCULATE (
        [Overall_Total Actual Prgrs],
        ALLSELECTED ( 'DIM Calendar' ),
        'DIM Calendar'[Date] <= MaxDate
    )
    • ajisharavind_99's avatar
      ajisharavind_99
      Icon for Advocate I rankAdvocate I
      3 years ago

      My budget calculation was the problem. When I applied for the cumulative as of today, it was also filtering budget. I removed the filter (REMOVEFILTERS), and the problem has been resolved.
      I appreciate your response