Dax Measure to get the Cumilative total Based on Part_Id

[abhijeetbehuria]

Thanks in Advance .

The above one is my source table .I want to get the output as  :-

From the Min date to the max date for the specific Part Id ,i want to get the cumilative total measure(not calculated column).

Also i want to find by using that measure ,when Part_Id has crossed 5 rupees .Like below :-

Please Help me .

[Greg_Deckler · Accepted solution]

@abhijeetbehuria Try:

Cumulative Total Measure = 
  VAR __PartID = MAX('Table'[Part_Id])
  VAR __Date = MAX('Table'[Month Date])
  VAR __Table = FILTER(ALLSELECTED('Table'),[Part ID] = __PartID && [Month Date] <= __Date)
  VAR __Result = SUMX(__Table, [Amount])
RETURN
  __Result

and

5 Rupee Measure = 
  VAR __PartID = MAX('Table'[Part_Id])
  VAR __Date = MAX('Table'[Month Date])
  VAR __Table = FILTER(ALLSELECTED('Table'),[Part ID] = __PartID && [Month Date] <= __Date)
  VAR __WhileLoop =
    ADDCOLUMNS(
      __Table,
      "__Cumulative", SUMX(FILTER(__Table, [Month Date] <= EARLIER([Month Date])),[Amount])
  VAR __Result = MINX(FILTER(__WhileLoop, __Cumulative >= 5), [Month Date])
RETURN
  __Result

[abhijeetbehuria]

Yes I got the Result .I need to use squre bracket around that to get the result .Thank You so much man.

[abhijeetbehuria]

Can i apply Filter like Month ,Year ,Quarter to this Measure 

[Greg_Deckler]

@abhijeetbehuria Should be able to since using ALLSELECTED instead of ALL. ALLSELECTED should keep any filters external to the visual itself.

[abhijeetbehuria]

Thank You so much

[Greg_Deckler · Accepted solution]

@abhijeetbehuria Try:

Cumulative Total Measure = 
  VAR __PartID = MAX('Table'[Part_Id])
  VAR __Date = MAX('Table'[Month Date])
  VAR __Table = FILTER(ALLSELECTED('Table'),[Part ID] = __PartID && [Month Date] <= __Date)
  VAR __Result = SUMX(__Table, [Amount])
RETURN
  __Result

and

5 Rupee Measure = 
  VAR __PartID = MAX('Table'[Part_Id])
  VAR __Date = MAX('Table'[Month Date])
  VAR __Table = FILTER(ALLSELECTED('Table'),[Part ID] = __PartID && [Month Date] <= __Date)
  VAR __WhileLoop =
    ADDCOLUMNS(
      __Table,
      "__Cumulative", SUMX(FILTER(__Table, [Month Date] <= EARLIER([Month Date])),[Amount])
  VAR __Result = MINX(FILTER(__WhileLoop, __Cumulative >= 5), [Month Date])
RETURN
  __Result

[Anonymous]

Sir Please help me to Understand these two measure. I got the solution but not be able to understand it. It's my humble request. 

[abhijeetbehuria]

It's giving me Error .

[Greg_Deckler · Accepted solution]

@abhijeetbehuria Sorry, missed a )

5 Rupee Measure = 
  VAR __PartID = MAX('Table'[Part_Id])
  VAR __Date = MAX('Table'[Month Date])
  VAR __Table = FILTER(ALLSELECTED('Table'),[Part ID] = __PartID && [Month Date] <= __Date)
  VAR __WhileLoop =
    ADDCOLUMNS(
      __Table,
      "__Cumulative", SUMX(FILTER(__Table, [Month Date] <= EARLIER([Month Date])),[Amount])
    )
  VAR __Result = MINX(FILTER(__WhileLoop, __Cumulative >= 5), [Month Date])
RETURN
  __Result

[MadhuKumar]

Thanks Greg, this is working