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
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
Hello Guys,
I want to add a new calculated row everyday automatically based on the data available in tables Mack VORS and Volvo VORS. All the columns in both VORS table has column which has values 0-1,2-7,8-14,>14 and I want to calculate it everyday automatically and add the calculated value in the row with date of the day as date value. Is this possible?
Thanks!
Failed to resolve name 'Addrows". It is not a valid table, variable or function name. @Yuliana Gu
Addrows is not a valid table, variable, or function name @Yuliana Gu
Hello Yuliana,
I have the same issue in my query.
I have a column which gives me a date and then another column which gives me how many days should I consider after that date.
I need to use this second column to create new rows putting each following date below the original date that the first column gave me. So, the output must be that in the date column, every date should be inputted.
I saw your solution to this post's question, but I'm not sure where should I put these formulas you described. Is it in the Advanced Query Editor? I've tried here, unsuccessfully.
Can you help me?
Thanks!
Hi @thiagozaiden,
The formulas above are used to create calculate table. One formula stands for one table.
Thanks,
Yuliana Gu
Hello @v-yulgu-msft,
I keep getting this error when trying the formulas:
Any ideia how to solve this?
Thanks!
Hi @thiagozaiden,
Do you directly copy the formula in my original post and then paste it into the box? Still have this error?
Thanks,
Yuliana Gu
Thanks Dan80 for the quick reply but the number of months can be any number i.e. it can also be greater than 100 in some cases.
Hence, creating different tables is not a solution.
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |