Struggling with getting right total.

Question

I'm sure this is easy, but spent 2 days and still cant work it out!!!&nbsp;I have a table with various projects and how many hours each is expected ot take as belowI want to create a date table the gives me the totla as each project comes on line such as below&nbsp;I used this measureMeasure = 
CALCULATE(       sum('Sales Pipeline'[Est Hours]),
           FILTER(
                 'Sales Pipeline',
                 MIN('Sales Pipeline'[Close Date])&lt;=MAX(DateTable[Day]) 
                  )
          )&nbsp;but you can see the total should be 550 on first row and only add in new projects as I pass the close date. I tried adding a SUMX clause on top but made no differnece. I'm completely stumped. I assume the issue is for the totla column it is looking up the MIN of [Close date] over whole table.&nbsp;Can someone intelligence point me in the right direction as sure i've done this before without issue?&nbsp;ThanksMike&nbsp;

amitchandak · Accepted Answer

I doubt MIN&nbsp;Measure = 
CALCULATE(       sum('Sales Pipeline'[Est Hours]),
           FILTER(
                 'Sales Pipeline',
                 ('Sales Pipeline'[Close Date])&lt;=MAX(DateTable[Day]) 
                  )
          )

or 
Measure = 
CALCULATE(       sum('Sales Pipeline'[Est Hours]),
           FILTER(
                 'Sales Pipeline',
                 ('Sales Pipeline'[Close Date])&lt;=MAX(DateTable[Day]) &amp;&amp;
('Sales Pipeline'[Close Date])&gt;=MIN(DateTable[Day])
                  )
          )Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @ Thanks. My Recent Blog -Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trendPower-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-RangesConnect on Linkedin

Forum Discussion

Struggling with getting right total.

2 Replies

Related content

Struggling to display correct Total using Measures

Struggling with getting a total from component column

Getting Totals On Each Row

Struggling to get the average

Struggling to get summary number when using table expression