Development months based on the Year of Account

[EricShahi]

Hi All,

I need to create a new table that will have development months. I'm able to get the start and end year from the another table that contains Year of account.

Lets Say the start year is 2021 and the end year is 2023, I want to see below table as a development month , I can easily get this done in SQL but this has be done in DAX. Can anyone help.

[Anonymous]

Hi @EricShahi ,

What sql formula are you using?

Best Regards,
Adamk Kong

[EricShahi]

i believe we need to make this table bit more dynamic 

month_ = GENERATESERIES ( 1, 36, 1 )

generate the months based on the year of account 

We wont have 36 months for each year of account. so need to make this change to make it work. 

 

[Anonymous]

Hi @EricShahi ,

You can try formula like below:

month_ = GENERATESERIES ( 1, 36, 1 )

year_ = 
VAR StartYear =
    MIN ( 'YearOfAccount'[Year] )
VAR EndYear =
    MAX ( 'YearOfAccount'[Year] )
VAR AllYears =
    GENERATESERIES ( StartYear, EndYear, 1 )
return AllYears

result_ =
VAR table_ =
    CROSSJOIN ( year_, month_ )
RETURN
    ADDCOLUMNS (
        table_,
        "a",
            INT ( ( [Value1] - 1 ) / 12 ) + [Value2]
                & IF (
                    MOD ( [Value1], 12 ) = 0,
                    12,
                    IF (
                        MOD ( [Value1], 12 ) >= 10,
                        MOD ( [Value1], 12 ),
                        "0" & MOD ( [Value1], 12 )
                    )
                )
    )

Best Regards,
Adamk Kong

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

[EricShahi]

Hey Thanks for this its works fine for 2021, but as we go to 2022 , the development months joing from 1 jan 2022 till dec 2023 should be 24, not 36..

[EricShahi]

@amitchandak @Daniel29195 @lbendlin