Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I'm stuck in one of the business use case where we need to add duplicate entries in a new custom table from the inputs given in other table.
e.q. consider i have a table with State, District, Date and #Months as the columns
State | Region | #Month | Date |
TN | HYD | 3 | 01-Oct-16 |
Now we want to dynamically create a custom table which will have duplicate entries with the same State, Region but the date being pushed to the next month based on the #Month Column
Expected Output is
State | Region | Month | Date |
TN | HYD | 3 | 01-Nov-16 |
TN | HYD | 3 | 01-Dec-16 |
TN | HYD | 3 | 01-Jan-17 |
As the #Month is 3 the 3 rows need to be pushed to the next three months.
Solved! Go to Solution.
The thread to ahieve your requirement is: new calendar table, cross join tables and filter the joined table.
You can take the below formulas as a reference:
CalendarTable = CALENDAR ( DATE ( 2015, 1, 1 ), DATE ( 2017, 12, 31 ) ) FilterCalendar = CALCULATETABLE ( CalendarTable, FILTER ( CalendarTable, CalendarTable[Date].[Day] = 1 ) ) CrossTable = ADDCOLUMNS ( CROSSJOIN ( AddRows, FilterCalendar ), "DateDiff", IF ( AddRows[Date] > FilterCalendar[Calendardate], DATEDIFF ( FilterCalendar[Calendardate], AddRows[Date], MONTH ), DATEDIFF ( AddRows[Date], FilterCalendar[Calendardate], MONTH ) ) ) FilterCrossTable = SELECTCOLUMNS ( CALCULATETABLE ( CrossTable, FILTER ( CrossTable, CrossTable[Date] < CrossTable[Calendardate] && CrossTable[DateDiff] <= CrossTable[Month] ) ), "State", CrossTable[State], "Region", CrossTable[Region], "Month", CrossTable[Month], "Date", CrossTable[Calendardate] )
Best regards,
Yuliana Gu
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |