How can I write a formula for incremental addition similar to += function?

[e9801591]

I am trying to create f field that would show the consistency of the values per month but still can't figure it out. I'm fairly new at this and would appreciate the help!

Need to create a measure that calculates the Factor. If Previous month's value = 0 then Factor = Value, else Factor = +=1

For Example, for March, Factor= 1 since previous month's factor is zero. For April, Factor = 2 (prev month's factor +1) since the previous month's value is not zero.

Month	Value	Factor
Jan	0	0
Feb	0	0
Mar	2	1
Apr	1	2
May	1	3
Jun	1	4
Jul	0	0
Aug	0	0
Sept	2	1
Oct	2	2
Nov	0	0
Dec	0	0

Thanks in advance

[v-diye-msft · Accepted solution]

Hi @e9801591 

Please let me know if you'd like to get below results:

Method 1. Created the calculated column:

Column = 
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        'Table',
        'Table'[date] <= EARLIER ( 'Table'[date] )
            && 'Table'[Value] <> 0
    )
)

Column 3 = 
VAR c = 'Table'[date]
VAR pre =
    CALCULATE (
        MAX ( 'Table'[date] ),
        FILTER ( 'Table', 'Table'[date] < c && 'Table'[Value] = 0 )
    )
RETURN
    IF (
        'Table'[Value] <> BLANK (),
        CALCULATE (
            COUNTROWS ( 'Table' ),
            FILTER (
                'Table',
                'Table'[date] <= EARLIER ( 'Table'[date] )
                    && 'Table'[Value] <> 0
            )
        )
            - CALCULATE ( MAX ( 'Table'[Column] ), FILTER ( 'Table', 'Table'[date] = pre ) )
    )

Method 2. 

Create the measure:

Measure = 
VAR c = MAX('Table'[date])
VAR pre =
 CALCULATE (
 MAX ( 'Table'[date] ),
 FILTER ( ALL('Table'), 'Table'[date] < c && 'Table'[Value] = 0 )
 )
RETURN
 IF (
 SUM('Table'[Value]) <> BLANK (),
 CALCULATE (
 COUNTROWS ( 'Table' ),
 FILTER (
 ALL('Table'),
 'Table'[date] <= MAX('Table'[date])
 && 'Table'[Value] <> 0
 )
 )
 -CALCULATE ( MAX ( 'Table'[Column] ), FILTER ( ALL('Table'), 'Table'[date] = pre ) )
 )

Pbix attached.

[amitchandak]

make sure you have date table join to date of you table, then you can try like

last MTD Sales = CALCULATE(SUM(Table[value]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

factor= if([last MTD Sales ]=0,1,last MTD Sales +1)

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin

[e9801591]

Thank you @amitchandak ! I see what you mean, but I don't think the formula checks all the preerquisites e.g. if the existing MTD sales > 0.

- if MTD sales =0 AND LM sales = 0;  then factor = 0

- if MTD sales >0 AND LM sales = 0; then factor = 1

- if MTD sales >0 and LM sales > 0; then factor= LM factor + 1

The formla provided below

factor= if([last MTD Sales ]=0,1,last MTD Sales +1)

 would return

- if MTD sales =0 AND LM sales = 0;  then factor = 1

[amitchandak]

Something like this

switch(true(),
[MTD sales] =0 && [LM sales] = 0, 0,
[MTD sales] >0 && [LM sales] = 0, 1,
[LM factor] + 1
)

[e9801591]

Thanks @amitchandak 

created this

1. LM FACTOR = CALCULATE([FACTOR],PARALLELPERIOD('DATE TABLE'[Date],-1,MONTH),ALL('DATE TABLE'[Month],'DATE TABLE'[Month Name]))

2. FACTOR = switch(true(),

     [MTD]=0 && [LM]=0,0,

     [MTD]>0 && [LM]=0,1,

     [LM FACTOR]+1)

Applied these and this is how it looks like, with the values for Jun, Jul are incorrect

	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
Person 1 - Factor	0	0	1	2	3	3	3	0	1	2	3	0

Reference data

	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
Person 1 Value	0	0	2	1	1	1	0	0	2	2	0	0

Expected factor should be like this:

	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
Person 1 Factor	0	0	1	2	3	4	0	0	1	2	0	0

[e9801591]

Tried the following but I am not getting the correct factor

Name	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
Person 1	0	0	2	1	1	1	0	0	2	2	0	0

I have the following measures

1. LM - this is just 0 or 1. 0 if LM value = 0, 1 if LM value > 0 

2. MTD - this is just 0 or 1. If MTD value =0, 1 if MTD value > 0

3. Factor =if ([LM] = 0 && [MTD] = 0,0, if([LM]=0 && [MTD] >0,1, [LM]+1))