How to create a calculated table based on a table with data ranges?

[Anonymous]

There is a table with date ranges (yyyymm format) :

Id	Start Period	End Period
1	202001	202002
2	202005	202007

I would like to create a calculated table with this transformed data:

Id	Period
1	202001
1	202002
2	202005
2	202006
2	202007

Is it possible to do it using DAX expression?

[amitchandak]

@Anonymous , Create a period table with all periods. and have a measure like this

New measure = CALCULATE(COUNTx(FILTER(Table,Table[Start Period ]<=max('Period'[Period]) && ( Table[End Period]>max('Period'[Period]))),(Table[Id])))

Put this it visual with ID from your table Period from the Period table and the new measure

do not join period with your table

also refer

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

[Anonymous · Accepted solution]

Sorry, I misunderstood the requirement... Here's the correct solution (even though this calculation should be done in Power Query).

// Assuming period is int

[Unpivoted Table] =
SELECTCOLUMNS(
    generate(
        T,
        var __start = T[Start Period]
        var __startDate =
            var __year = int( __start / 100 )
            var __month = mod( __start, 100 )
            var __day = 1
            return
                date( __year, __month, __day )
        var __end = T[End Period]
        var __endDate =
            var __year = int( __end / 100 )
            var __month = mod( __end, 100 )
            var __day = 1
            return
                date( __year, __month, __day )            
        return
            DISTINCT(
                SELECTCOLUMNS(
                    CALENDAR( __startDate, __endDate ),
                    "@Period", int( format( [Date], "yyyyMM" ) )
                )
            )
    ),
    "Id", [Id],
    "Period", [@Period]
)