Forum Discussion

jimmy7377's avatar
jimmy7377
Frequent Visitor
1 year ago
Solved

Cumulative headache in a matrix

Dear games Master,

 

I have a matrix table with the followng layers

 

Fiscal Year

Fiscal Month

Team

 

I have the Colum Cost which i am looking to do a cumulative calculate against the above structure

 

First Direction i wen tin was : 

Cost **bleep** = CALCULATE(SUM('All Teams'[Cost]), 'All Teams'[Date From] <= 'All Teams'[Date From] )
But this just gives me a direct reflection of the cost column which is no good
 
 
Then i went to : 
Cumulative Total =
VAR CurrentDate = MAX('All Teams'[WeekStart].[Date])
RETURN
    CALCULATE(
        SUM('All Teams'[Cost]),
        FILTER(
            ALL('All Teams'[WeekStart]),
            'All Teams'[WeekStart].[Date] <= CurrentDate
        )
 
Week start is also on my calenmder table but i moved it into the same table as the costs for the ease of waht im doing but can easily be changed back.
 
Please someone put me out of my misory
 

 

Need Help
  • jimmyd7377's avatar
    jimmyd7377
    1 year ago

    I could not get yours to work, the fiscal month and Year are on the calender table when i replaced them it moaned, but it did get me going and i got this to work

    Cumulative =

    CALCULATE

    (

    SUM

    ('All Teams'[Cost]),

    FILTER

    (

    ALL

    ('All Teams'), 'All Teams'[Date From] <=

    MAX

    ('All Teams'[Date From])))

     

     

     

    thansk for your assistance

2 Replies

  • PijushRoy's avatar
    PijushRoy
    Icon for Community Champion rankCommunity Champion
    1 year ago

    Hi jimmy7377 

    Can you please try the below DAX? If the below code not work, please keep me posted

    Cumulative Cost = 
VAR CurrentMonth = MAX('All Teams'[Fiscal Month])
VAR CurrentYear = MAX('All Teams'[Fiscal Year])
RETURN
    CALCULATE(
        SUM('All Teams'[Cost]),
        FILTER(
            ALL('All Teams'),
            'All Teams'[Fiscal Year] = CurrentYear &&
            'All Teams'[Fiscal Month] <= CurrentMonth
        )
    )

     

    • jimmyd7377's avatar
      jimmyd7377
      New Member
      1 year ago

      I could not get yours to work, the fiscal month and Year are on the calender table when i replaced them it moaned, but it did get me going and i got this to work

      Cumulative =

      CALCULATE

      (

      SUM

      ('All Teams'[Cost]),

      FILTER

      (

      ALL

      ('All Teams'), 'All Teams'[Date From] <=

      MAX

      ('All Teams'[Date From])))

       

       

       

      thansk for your assistance