How to get cumulative amount and cumulative % for Overall and based on Slicer

[NickzNickz]

Hi ... 
I have created table profit and data as below:

Date	Collection ($)	Costing ($)	Profit ($)
30/1/2023	4,000	9,421	(5,421)
24/2/2023	22,500	17,077	5,423
10/3/2023	25,500	18,157	7,343
11/3/2023	27,000	21,183	5,817
7/4/2023	1,950	7,301	(5,351)
1/6/2023	3,000	900	2,100
31/7/2023	12,750	6,978	5,772
31/7/2023	6,000	5,197	803
12/8/2023	32,000	21,678	10,322
5/9/2023	11,000	4,782	6,218
5/9/2023	4,550	3,980	570
23/10/2023	3,000	4,556	(1,556)
23/10/2023	31,500	7,411	24,089
24/10/2023	900	0	900
3/1/2024	5,000	3,339	1,661
9/1/2024	5,500	3,288	2,212
20/1/2024	4,000	2,027	1,973
29/1/2024	4,000	2,027	1,973
4/2/2024	17,000	12,444	4,556
10/2/2024	8,250	7,728	522
16/2/2024	15,000	14,061	939
20/2/2024	25,500	10,241	15,259

Based on above data, I need to create a measure for Cumulative Profit ($) and Cumulative Profit (%).
My dashboard setup, I have Year Slicer (dropdown / Multi-select with CTRL), Table, Card and Line Chart.
How can I get the value based on my slicer selection (All or Year Selected).
Regards,

NickzNickz

[v-yiruan-msft · Accepted solution]

Hi @NickzNickz ,

You can create the measure as below to get it, please find the details in the attachment.

Cumulative Profit ($) = 
CALCULATE (
    SUM ( 'Table'[Profit ($)] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Date] <= SELECTEDVALUE ( 'Table'[Date] )
    )
)

Cumulative Profit (%) = 
VAR _all =
    CALCULATE ( SUM ( 'Table'[Profit ($)] ), ALLSELECTED ( 'Table' ) )
RETURN
    DIVIDE ( [Cumulative Profit ($)], _all )

Best Regards

[Jihwan_Kim]

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the  below picture and the attached pbix file.

Profit: = 
SUMX(Data, Data[Collection ($)]-Data[Costing ($)])

WINDOW function (DAX) - DAX | Microsoft Learn

Cumulative profit %: = 
VAR _allselectedprofit =
    CALCULATE (
        [Profit:],
        WINDOW (
            1,
            ABS,
            -1,
            ABS,
            ALLSELECTED ( 'Calendar'[Date], 'Calendar'[Year]),
            ORDERBY ( 'Calendar'[Date], ASC )
        )
    )
VAR _cumulativeprofit =
    CALCULATE (
        [Profit:],
        WINDOW (
            1,
            ABS,
            0,
            REL,
            ALLSELECTED ( 'Calendar'[Date], 'Calendar'[Year] ),
            ORDERBY ( 'Calendar'[Date], ASC )
        )
    )
RETURN
    IF (
        NOT ISBLANK ( [Profit:] ),
        DIVIDE ( _cumulativeprofit, _allselectedprofit )
    )

[NickzNickz]

Hi @Jihwan_Kim ,

I am not sure how your semantic model looks like > My date data in the same table.
How can I get value of Cumulative Profit ($).

Btw do i need to create additional column for Year and Month. My date is in Date level and my data in dashboard is by Month and my slicer is in Year.

Example:

Regards,

NickzNickz