Forum Discussion

Newcolator's avatar
Newcolator
Helper II
3 years ago
Solved

Cumulating fields based on value in another table

Hello I have a table that has two columns that contain a month number, like the orange one below. I want to create the blue table that cumulatively counts the number of rows that contain each month ...
Need Help
  • amitchandak's avatar
    amitchandak
    3 years ago

    Newcolator , Create a new table with the month number

     

    join with both columns, assume Month 1 is active and Month 2 is  inactive

     

    M1= count(Table[Month1])

     

    M2= calculate(count(Table[Month2]), USERELATIONSHIP(Table[Month2] , Month[Month] ) )

     

    Then you can cumulative like

     

    Cumm M1= CALCULATE([M1],filter(all('Month'),'Month'[Month] <=max('Month'[Month])))

     

    Cumm M2= CALCULATE([M2],filter(all('Month'),'Month'[Month] <=max('Month'[Month])))

     

    You can also try window

    Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc

amitchandak's avatar
amitchandak
Super User
3 years ago

Newcolator , Create a new table with the month number

 

join with both columns, assume Month 1 is active and Month 2 is  inactive

 

M1= count(Table[Month1])

 

M2= calculate(count(Table[Month2]), USERELATIONSHIP(Table[Month2] , Month[Month] ) )

 

Then you can cumulative like

 

Cumm M1= CALCULATE([M1],filter(all('Month'),'Month'[Month] <=max('Month'[Month])))

 

Cumm M2= CALCULATE([M2],filter(all('Month'),'Month'[Month] <=max('Month'[Month])))

 

You can also try window

Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc