Power bi issue - I need to achieve the Month Column - Please help

[santoshrnath]

Irrespective of what the start date is, the M2, M3 , M4 should take the next month of the M1 start date and it is grouped into Projects also (the first column). 

[Baskar]

Hi Santhosh ,

Try this one it will help u .

Here 'Test Table' as Your Table name 

Month_Name =
var Proj_Name = 'Test Table'[Project]
var Len_Group = len('Test Table'[Group]) - 1
var Group_Number = value(Right( 'Test Table'[Group] , Len_Group))

var Result =IF(Group_Number=1, FORMAT('Test Table'[Start Date],"MMMM") ,
FORMAT( CALCULATE( DATEADD('Test Table'[Start Date],Group_Number-1,MONTH) ,
FILTER(ALL('Test Table'),AND('Test Table'[Project]=Proj_Name,
'Test Table'[Group]="M1"))
) , "MMMM")
)

return Result

Let me know if it is not helping u dude 

[santoshrnath]

Hi Baskar,

The Code is Fantastic.

One more help, can the year also be associated with month and also there can be empty date column, But M1 will always have the date. It has to add one month based on the M1 column with the year attached to it.

[Baskar]

Cool Man we can do.

Relpace "MMM" with "MMM-YY"

It will made u happy .

Cheers 🙂

[santoshrnath]

Thanks Baskar

One more small issue, there are blank values in the table. I am not able to know y. 

[Baskar]

Can u please give your Start Date

[santoshrnath]

The start date is 01/07/2016 for the above project. But the count is moving fine, suddenly some spaces are getting created in between. For other project it shows full and some it skips M21 etc.

[Baskar]

Hi Santhosh ,

Actually prob in Dax end , please have a look on this link 

https://blog.crossjoin.co.uk/2012/01/06/the-dax-dateadd-function-and-missing-dates/

but anyhow i will help with another way , give me some more time 🙂

[santoshrnath]

Thanks Baskar. 

[v-qiuyu-msft]

Hi @santoshrnath,

Was the suggestion from @Baskar helpful for you? Was the issue solved?

Best Regards,
Qiuyun Yu

[santoshrnath]

Yes

[v-qiuyu-msft]

@santoshrnath Glad to hear the issue is gone. would you please mark a helpful reply as an answer so that we can close this thread?

Best Regards,
Qiuyun Yu

[santoshrnath]

Hi Baskar,

The Code is Fantastic.

One more help, can the year also be associated with month and also there can be empty date column, But M1 will always have the date. It has to add one month based on the M1 column with the year attached to it.