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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
seanguerrero
Helper I
Helper I

Generate Custom Table of DATES and CROSSJOIN

Hello everyone,

 

I need a PBI expert's help. Please refer to the table I've copied over. I need to generate a table of custom dates and information. Essentially using this table, I need DAX to generate a table/list of all Mondays [DDD] from the 1st week of the month [Month_Week_Num] for the given timeframe [Block Effective Start Date] - [Block Effective End Date] for each of the [Surgeons] which I've blinded. 

 

I think it's a matter of using a combination of FILTER CROSSJOIN UNION, etc but I can't seem to get it to work. 

 

For instance, just looking at Surgeon 1, the table should look like:

 

1/3     Mon     Surgeon A

2/7     Mon     Surgeon A

3/7     Mon     Surgeon A

4/4     Mon     Surgeon A

5/2     Mon     Surgeon A

6/6     Mon     Surgeon A

1/3     Mon     Surgeon B

2/7     Mon     Surgeon B

3/7     Mon     Surgeon B

4/4     Mon     Surgeon B

5/2     Mon     Surgeon B

6/6     Mon     Surgeon B

 

Any help would be appreciated. 

 

seanguerrero_0-1658449640487.png

 

1 ACCEPTED SOLUTION

image.png

 

Table 2 = GENERATE('Table',GENERATESERIES(YEAR('Table'[Start_date])*12+MONTH('Table'[Start_date]),YEAR('Table'[End_date])*12+MONTH('Table'[End_date])))

 

block_day =
var YYMM = [Value]-1
var _1stOfMonth = DATEVALUE(INT(yymm/12)&"/"&MOD(yymm,12)+1&"/1")
var _block_day = _1stOfMonth - WEEKDAY(_1stOfMonth,2) +1 +[Month_week_num]*7+[DDD]-1
return IF(_block_day>=[Start_date] && _block_day<=[End_date],_block_day,BLANK())

View solution in original post

9 REPLIES 9
seanguerrero
Helper I
Helper I

@vapid128 I think you are getting closer! 

 

The photo I attached is a better explanation of what I am looking for and I think you are on the right track of using GENERATE/GENERATESERIES. Example (referring to the photo)....The table on the left is the schedule table that is manually entered, the table on the right is what I would like to generate.

 

Example: Employee A, was scheduled to work on the Monday of the 1st week of every month between Start Date 1/1/2022 and end date 6/30/2022. Employee B was scheduled to work on every third Tuesday between his start date 5/1/2022 and 7/1/2022. Employee C was scheduled to work on every 2nd Thursday from 2/1/2022 to 5/30/2022.

 

 

seanguerrero_0-1658508431652.png

 

This is awesome @vapid128 ! One question, how is [Values] generated? These are all new concepts to me so I'm interested in learning more!  Thanks again! You've been extremely helpful!

 

It is a combo formule

GENERATE(Table,GENERATESERIES(start number from table, end number from table))

 

I create a index: year*12+month

 

If we use YYYYMM like 202201 to 202206, it will generate 202201/202202/202203/202204/202205/202206.

When your date is from 202112 to 202206 it will generate 202112/202113/202114/..../202206.

 

Therefore, we need rewirte this to year*12+month as a index.

And use 

var YYMM = [Value]-1
var _1stOfMonth = DATEVALUE(INT(yymm/12)&"/"&MOD(yymm,12)+1&"/1")
wirte back as first day of each month

@vapid128 I have Start_Date starting from previous years. Any year < 2022 is giving block_day a blank. Suggestions?

If the blank is in block_day colnum,

that is because 

return IF(_block_day>=[Start_date] && _block_day<=[End_date],_block_day,BLANK())

 

 

image.png

 

Table 2 = GENERATE('Table',GENERATESERIES(YEAR('Table'[Start_date])*12+MONTH('Table'[Start_date]),YEAR('Table'[End_date])*12+MONTH('Table'[End_date])))

 

block_day =
var YYMM = [Value]-1
var _1stOfMonth = DATEVALUE(INT(yymm/12)&"/"&MOD(yymm,12)+1&"/1")
var _block_day = _1stOfMonth - WEEKDAY(_1stOfMonth,2) +1 +[Month_week_num]*7+[DDD]-1
return IF(_block_day>=[Start_date] && _block_day<=[End_date],_block_day,BLANK())
seanguerrero
Helper I
Helper I

Thanks for replying! The table I attached will eventually have different variations of Weeks and Days, so for instance the table in the attachment will have the second Tuesday of the month, third Thursday of the month, etc... so how should build it?

updated solution

vapid128
Solution Specialist
Solution Specialist

image.png

 

Table = GENERATE(VALUES(R_ID[restaurant_id]),GENERATESERIES(1,12))
 
Column =
var YearStart = DATEVALUE("2022/1/1")
var yymm =YEAR(YearStart)*12+MONTH(YearStart)+[Value]-2
var _1stOfMonth = DATEVALUE(INT(yymm/12)&"/"&MOD(yymm,12)+1&"/1")
return _1stOfMonth - WEEKDAY(_1stOfMonth,2) +8

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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