Help with Cumulative DAX Measure with Filter

Question

Hello community,I am new to DAX and need help with what I am working on in PBI. I am sorry my writeup is a bit wordy.&nbsp;The cumulative cash flow for each project start from the beginning of the project till the end. For projects still ongoing, it cumulates till the current date. I applied period slicer for Year and Month. What I am trying to achieve is that for any particular period, only the projects active during that period should be returned on the table i.e for 2017, I want to see data for only Jobs 1707, 1702, 1703, while others are filtered out. Also if I chose 2021 on my slicer, I would like to see data for Jobs 2001, 2003, 2004, 2101, 2102, 2103, 2105 because they are the only jobs active during the period. I am able to achieve this on the table only when the cumulative measure is not added. Once it is added and a period in the slicer is selected, the table returns all the Job number. The cumulative measure is needed because the net Cash and cumulative net Cash for ongoing projects are not the same in the same period.&nbsp;The DaX in&nbsp;cumulative net Cash Flow measure is as follows:&nbsp;Cumulative Net Cash Flow = CALCULATE(&nbsp;&nbsp;&nbsp;&nbsp;[Net Cash Flow],&nbsp;&nbsp;&nbsp;&nbsp;FILTER(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ALLSELECTED('DimDate'[Date]),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ISONORAFTER('DimDate'[Date], MAX('DimDate'[Date]), DESC)&nbsp;&nbsp;&nbsp;&nbsp;))&nbsp;**DimDate is my Dimension Date Table.&nbsp;Because the Cash, Cashout and Project Numbers are all in different tables, I used a dimension Date table in my model. Any help with on how to filter out Jobs that are not active during a period will be appreciated.&nbsp;**A sample table is attached. Data are representative only.&nbsp;Job NumberStart YearStart MonthLast Transaction YearLast Transaction MonthTotal Cash inTotal Cash outNet CashCum Net Cash Flow1701201702201905&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10,000.00&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4,500.00&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5,500.00&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5,500.001702201704201908&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 12,000.00&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9,000.00&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3,000.00&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3,000.001703201706202002&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 23,000.00&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 19,874.00&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3,126.00&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3,126.001802201807202008&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,800.00&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 12,785.00&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (10,985.00)&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (10,985.00)1803201807202107&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2,300.00&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 20,132.00&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (17,832.00)&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (17,832.00)1805201804201908&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 35,000.00&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 22,983.00&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 12,017.00&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 12,017.001903201909202010&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 12,000.00&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 20,964.00&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (8,964.00)&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (8,964.00)1905201907202103&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 25,000.00&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 18,765.00&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6,235.00&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6,235.001906201908202009&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 33,000.00&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 21,098.00&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 11,902.00&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 11,902.002001202003202111&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 45,000.00&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 65,324.00&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (20,324.00)&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (32,980.00)2002202004202012&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 60,888.00&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 23,782.00&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 37,106.00&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 37,106.002003202002202107&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 39,766.00&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 11,896.00&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 27,870.00&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 27,870.002004202005202109&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 22,545.00&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 27,476.00&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (4,931.00)&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (3,338.00)2101202106202111&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 23,454.00&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 35,447.00&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (11,993.00)&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (5,467.00)2102202106202110&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 12,678.00&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 15,365.00&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (2,687.00)&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4,056.002103202109202111&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 44,754.00&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 63,785.00&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (19,031.00)&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (24,656.00)2105202103202111&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 33,907.00&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 32,765.00&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,142.00&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (2,000.00)

fuulhouse · Accepted Answer

Thank you&nbsp;v-yanjiang-msft&nbsp;. I looked at the sample you created and it&nbsp;did the magic.The Date table is connected to the fact table by a Datekey. Also, the 1803 and 1905 thing were typos.&nbsp;&nbsp;

Forum Discussion

Help with Cumulative DAX Measure with Filter

2 Replies

Related content

Cumulative Total

Cumulative Difference from Another measure

Cumulative Measure

Transform measure in cumulative measure

Cumulative measure / 100

1701	2017	02	2019	05	$ 10,000.00	$ 4,500.00	$ 5,500.00	$ 5,500.00
1702	2017	04	2019	08	$ 12,000.00	$ 9,000.00	$ 3,000.00	$ 3,000.00
1703	2017	06	2020	02	$ 23,000.00	$ 19,874.00	$ 3,126.00	$ 3,126.00
1802	2018	07	2020	08	$ 1,800.00	$ 12,785.00	$ (10,985.00)	$ (10,985.00)
1803	2018	07	2021	07	$ 2,300.00	$ 20,132.00	$ (17,832.00)	$ (17,832.00)
1805	2018	04	2019	08	$ 35,000.00	$ 22,983.00	$ 12,017.00	$ 12,017.00
1903	2019	09	2020	10	$ 12,000.00	$ 20,964.00	$ (8,964.00)	$ (8,964.00)
1905	2019	07	2021	03	$ 25,000.00	$ 18,765.00	$ 6,235.00	$ 6,235.00
1906	2019	08	2020	09	$ 33,000.00	$ 21,098.00	$ 11,902.00	$ 11,902.00
2001	2020	03	2021	11	$ 45,000.00	$ 65,324.00	$ (20,324.00)	$ (32,980.00)
2002	2020	04	2020	12	$ 60,888.00	$ 23,782.00	$ 37,106.00	$ 37,106.00
2003	2020	02	2021	07	$ 39,766.00	$ 11,896.00	$ 27,870.00	$ 27,870.00
2004	2020	05	2021	09	$ 22,545.00	$ 27,476.00	$ (4,931.00)	$ (3,338.00)
2101	2021	06	2021	11	$ 23,454.00	$ 35,447.00	$ (11,993.00)	$ (5,467.00)
2102	2021	06	2021	10	$ 12,678.00	$ 15,365.00	$ (2,687.00)	$ 4,056.00
2103	2021	09	2021	11	$ 44,754.00	$ 63,785.00	$ (19,031.00)	$ (24,656.00)
2105	2021	03	2021	11	$ 33,907.00	$ 32,765.00	$ 1,142.00	$ (2,000.00)