Need to calculate cumulative on measure year/month wise

[Anonymous]

Hi All,

I need to to create on power bi something like below which is to create on Exel but not sure how to do it on power BI

Output:

Year	PERIOD	ACTIVE	CREATED (Cumulative)	DELETED (Cumulative)	NET IMPACT (Cumulative)	CREATED	DELETED
2020	Jan	122877	2,177	664	2,841	2177	664
2020	Feb	28276	30,754	-1,22,514	-91,760	28577	-1,23,178
2020	Mar	27099	57,936	-1,50,873	-92,937	27182	-28,359
2020	Apr	128705	59,992	-51,323	8,669	2056	99,550

Here 'ACTIVE' and 'CREATED' values  are already there, based on that I need to create other columns.

Deleted formula should be : H3(-1,23,178) =-C2 (12,287)-G3(28,577)+C3(28,276)

Created (Cumulative) : D3(30,754) = D2(2,177)+G3(28,577)

Deleted (Cumulative) : E3(-1,22,514) = E2(664)+H3(-1,23,178)

Input Data:

Date	ACTIVE	CREATED
12-12-2019	1300	21974
18-12-2019	2100	22300
12-01-2020	2000	1100
13-01-2020	20877	177
14-01-2020	50000	400
15-01-2020	50000	500
16-02-2020	10276	8000
17-02-2020	10000	577
18-02-2020	5000	10000
19-02-2020	2000	5000
20-02-2020	1000	5000
26-03-2020	5000	10000
22-03-2020	6099	6182
23-03-2020	16000	11000
13-04-2020	2000	1000
14-04-2020	705	56
15-04-2020	10000	1000

Only in the month of January the CREATED (Cumulative) and DELETED (Cumulative) will be same as Created and Deleted.

I am able to create Created (Cumulative) but it is not getting reset in every jan of each year.

Please find the link of the dashboard for better understanding help me with the solution of this issue.

https://app.powerbi.com/view?r=eyJrIjoiNmFjNzM3ODItOTEwOS00NjE1LWIyMWMtNmMyMTRkM2ZkNTRjIiwidCI6IjVhM...

Thanks in advance.

[Anonymous]

Hi All,

Please find the dashboard attached where you can see how the data looks like. According to that I need to calculate above measures which I have mentioned above. 

Here I am facing difficulty with calculate deleted cumulative which is taking last date deleted value where I want last deleted value for the previous month. And Every year January it has to be recalculate from created value, then cumulative and created should be same.

https://app.powerbi.com/view?r=eyJrIjoiNmFjNzM3ODItOTEwOS00NjE1LWIyMWMtNmMyMTRkM2ZkNTRjIiwidCI6IjVhM...

Please let me know if you have any solution of this issue.

Data looks like this:

Date	ACTIVE	CREATED
12-12-2019	1300	21974
18-12-2019	2100	22300
12-01-2020	2000	1100
13-01-2020	20877	177
14-01-2020	50000	400
15-01-2020	50000	500
16-02-2020	10276	8000
17-02-2020	10000	577
18-02-2020	5000	10000
19-02-2020	2000	5000
20-02-2020	1000	5000
26-03-2020	5000	10000
22-03-2020	6099	6182
23-03-2020	16000	11000
13-04-2020	2000	1000
14-04-2020	705	56
15-04-2020	10000	1000

Thanks in advance

[v-alq-msft]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end. I assume that the default value for 'DELETED' is 664.

Table:

You may create an index column in 'Query Editor' as below.

Then you can create measures as below.

DELETED = 
var tab = 
ADDCOLUMNS(
    'Table',
    "Result",
    IF(
        [Year]=2020&&[PERIOD]="Jan",
        664,
        var _lastactive = LOOKUPVALUE('Table'[ACTIVE],'Table'[Index],[Index]-1)
        return
        -_lastactive-[CREATED]+[ACTIVE]
    )
)
return
SUMX(
    tab,
    [Result]
)

Created Cumulative = 
var tab = 
ADDCOLUMNS(
    'Table',
    "Result",
    CALCULATE(
        SUM('Table'[CREATED]),
        FILTER(
            ALL('Table'),
            'Table'[Index]<=EARLIER('Table'[Index])
        )
    )
)
return
SUMX(
    tab,
    [Result]
)

DELETED Cumulative = 
var tab = 
ADDCOLUMNS(
    'Table',
    "Result1",
    IF(
        [Year]=2020&&[PERIOD]="Jan",
        664,
        var _lastactive = LOOKUPVALUE('Table'[ACTIVE],'Table'[Index],[Index]-1)
        return
        -_lastactive-[CREATED]+[ACTIVE]
    )
)
var newtab = 
ADDCOLUMNS(
    tab,
    "Result2",
    var _index = [Index]
    var t = 
    ADDCOLUMNS(
        ALL('Table'),
        "Result1",
        IF(
            [Year]=2020&&[PERIOD]="Jan",
            664,
            var _lastactive = LOOKUPVALUE('Table'[ACTIVE],'Table'[Index],[Index]-1)
            return
            -_lastactive-[CREATED]+[ACTIVE]
        )
    )
    return
    SUMX(
        FILTER(
            t,
            [Index]<=_index
        ),
        [Result1]
    )
)
return
SUMX(
    newtab,
    [Result2]
)

NET IMPACT Cumulative = [Created Cumulative]+[DELETED Cumulative]

Result:

Best Regards

Allan

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

[Anonymous]

Hi Allan,

Thanks for your reply but the data starts from year 2004. So hard coded year and period and deleted value will not give the correct result.

I have given here the dummy data.

[richbenmintz]

Hi @Anonymous,

Have you tried to build a measure similar to 

Created (Cummulative) = CALCULATE(sum('Table'[CREATED]), FILTER(ALL('Table'), 'Table'[PeriodId] <= MAX('Table'[PeriodId]))) 

Please also include a .pbix file as it will make solving the problem with your data and data model much easier.

Richard

[richbenmintz]

Hi @Anonymous,

After adding a couple of columns to your data table

- PeriodId (yyyymm)

- PeriodNum(Month number used to sort the period column)

I created the following Measure as an example of how to created the cummulative total

Created (Cummulative) = CALCULATE(sum('Table'[CREATED]), FILTER(ALL('Table'), 'Table'[PeriodId] <= MAX('Table'[PeriodId]))) 

Hope this helps,

Richard 

Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

[amitchandak]

@Anonymous , for that you can ytd with a date calendar

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

refer:

https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.

[Greg_Deckler]

@Anonymous - There is a running total quick measure you can use in the Desktop. 

Otherwise, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.