Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

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

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

IdStart PeriodEnd Period
1202001202002
2202005202007

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

IdPeriod
1202001
1202002
2202005
2202006
2202007

Is it possible to do it using DAX expression?

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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]
)

 

 

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@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

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

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]
)

 

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.