How to write a Dax for after half year adding value based on 6th month

[Naveen_SV]

Hi Team,

I have a requirement first half of the year my cost will remain same, but later secound half of the year my cost should add 6th month cost.

Ex :       month           Cost

               1                   23

               2                   21

               3                  20

               4                  33

               5                  12

               6                   15

               7                  12+15

               8                   11+15

               9                   09+15

              10                 14+15

              11                  24+ 15

              12                 26+15

Above example is just a dummy  please let me know if this is possbile.

[Naveen_SV · Accepted solution]

Hi Amit,

I tried with both like creating Measure and column also but i am getting same error.

new column =

VAR _cost =

MAXX ( FILTER ( 'Table', 'Table'[Month] = 6 ), 'Table'[Total Cost] )

RETURN

IF ( 'Table'[Month] > 6, _cost + 'Table'[Total Cost] )

with measure

new measure =

VAR _cost =

MAXX ( FILTER ( 'Table', 'Table'[Month] = 6 ), 'Table'[Total Cost] )

RETURN

IF ( FILTER ( 'Table', 'Table'[Month] = 6 ), _cost, 'Table'[Total Cost] )

[v-jingzhang · Accepted solution]

Hi @Naveen_SV 

Please create a new column with below code. PBIX is attached. 

new cost = 
IF (
    'Table'[Month] > 6,
    MAXX ( FILTER ( 'Table', 'Table'[Month] = 6 ), 'Table'[Cost] ) + 'Table'[Cost],
    'Table'[Cost]
)

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Naveen_SV]

Hi Amit,

Can you send me pbi file please it will help

[amitchandak]

@Naveen_SV , try new column like

new column =
var _cost = maxx(filter(Table, [Year] =earlier([Year]) && [Month] =6), [Cost])
return
if([Month] >6 , _cost + [Cost], [Cost])

[Naveen_SV]

HI Amit,

thanks for your reply, i don't have this year column but i have only month in number, when i tried your

formuale i am getting this error pleas find the below table   

<pi>A circular dependency was detected: Invoice[Running total].</pi>

[amitchandak]

@Naveen_SV , Try like

new column =
var _cost = maxx(filter(Table, [Month] =6), [Cost])
return
if([Month] >6 , _cost + [Cost], [Cost])

[Naveen_SV · Accepted solution]

Hi Amit,

I tried with both like creating Measure and column also but i am getting same error.

new column =

VAR _cost =

MAXX ( FILTER ( 'Table', 'Table'[Month] = 6 ), 'Table'[Total Cost] )

RETURN

IF ( 'Table'[Month] > 6, _cost + 'Table'[Total Cost] )

with measure

new measure =

VAR _cost =

MAXX ( FILTER ( 'Table', 'Table'[Month] = 6 ), 'Table'[Total Cost] )

RETURN

IF ( FILTER ( 'Table', 'Table'[Month] = 6 ), _cost, 'Table'[Total Cost] )