Help with Cumulative DAX Measure with Filter

[fuulhouse]

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.

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.

The DaX in cumulative net Cash Flow measure is as follows:

Cumulative Net Cash Flow = CALCULATE(

    [Net Cash Flow],

    FILTER(

        ALLSELECTED('DimDate'[Date]),

        ISONORAFTER('DimDate'[Date], MAX('DimDate'[Date]), DESC)

    )

)

**DimDate is my Dimension Date Table.

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.

**A sample table is attached. Data are representative only.

Job NumberStart YearStart MonthLast Transaction YearLast Transaction MonthTotal Cash inTotal Cash outNet CashCum Net Cash Flow

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)

[v-yanjiang-msft]

Hi @fuulhouse ,

According to your description, if you want to calculate the accumulated value of net cash flow, you can write it like this:

Cumulative Net Cash Flow =
SUMX (
    FILTER (
        ALLSELECTED( 'Table' ),
        'Table'[Job Number] <= MAX ( 'Table'[Job Number] )
            && 'Table'[Start Year] = MAX ( 'Table'[Start Year] )
    ),
    [Net cash flow]
)

Is the value of Cumulative Net Cash Flow your expected outcome?

I have some unclear points about your sample:

1.Is Date column in DimDate table a date type? How does it make relationship with the table of Start Year, as they are not the same data type.

2.The Last Transaction Year of 1803 and 1905 is 2021, why didn’t them return when you choose 2021 on slicer?

I cannot reproduce your problem in my sample, I attach my sample below, would you modify it and post again? It’s best to reflect your problem in the sample, so that I know where the problem is.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[fuulhouse · Accepted solution]

Thank you @v-yanjiang-msft . I looked at the sample you created and it did the magic.

The Date table is connected to the fact table by a Datekey. Also, the 1803 and 1905 thing were typos.