Cumulating fields based on value in another table

[Newcolator]

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 number. I can achieve this with data transformations, but I'm wondering if it's possible to do this on the fly with DAX. I've tried creating a table, not linked, that just has a single column of month numbers 1-12 then using that field as my x-axis variable in a line chart. Then I just need to write some DAX to do a running total of rows where the month value in the row equals the relevant x-axis value. This would be a DAX formula working at row level, is that something to do with "each"? Does anyone know if this is possible? Hope that makes sense! Many thanks.

[amitchandak · Accepted solution]

@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