topic How to populate future value in date column according to due date column in DAX Commands and Tips

How to populate future value in date column according to due date column

Anonymous — Mon, 30 Oct 2023 09:54:17 GMT

Hi!
I am new to Power BI.

I have to allocate resource based on two field
1. Allocation Month-(Jan,Feb,Mar)
2. Start Period( Jan 2023, May 2024)

I need to populate future allocation according to month date
Example : If my allocation Start in May 2024 and Allocation Month (Jan,Mar,May,July)
My data should populate in May 2024,July 2024, Jan 2025,Mar 2025, etc

Is there any way I can populate date table according to this logic?

I want to populate like this for the hours

Re: How to populate future value in date column according to due date column

Anonymous — Wed, 01 Nov 2023 15:47:45 GMT

Hi Anonymous ,

Thanks for reaching out to us about this problem. I'm not clear about your requirement. Could you please provide some raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

Re: How to populate future value in date column according to due date column

Anonymous — Thu, 02 Nov 2023 04:13:54 GMT

I have figured out how to solve this in Power Query

First I create a today column in Power Query
Date.From(DateTime.LocalNow())

2. Then I create End Date based on how long I want the data to be populate

3. Then I create a custom column based on my first start date(GO LIVE DEL PERIOD) and End Date

{Number.From([#"GO LIVE(DEL PERIOD)"])..Number.From([EndDate])}

4. Expand the custom list

5. Changed the Custom column(Dates) into Date Type
6. Duplicate the column. (Dates-copy) This will be used to take only one ddate each month

7. Transform the column(Dates) into End of Month

8. Add Custom column for the condition to filter only the end of the month

9. Filter the condition =1

10. To filter the row based on due month, create another condition column
My data All mean will occur every month.

if [DUE MONTHS] = "All" then 1
else if Text.Contains([DUE MONTHS],"Jan") and Date.Month([End of Month])=1 then 1
else if Text.Contains([DUE MONTHS],"Feb") and Date.Month([End of Month])=2 then 1
else if Text.Contains([DUE MONTHS],"Mar") and Date.Month([End of Month])=3 then 1
else if Text.Contains([DUE MONTHS],"Apr") and Date.Month([End of Month])=4 then 1
else if Text.Contains([DUE MONTHS],"May") and Date.Month([End of Month])=5 then 1
else if Text.Contains([DUE MONTHS],"Jun") and Date.Month([End of Month])=6 then 1
else if Text.Contains([DUE MONTHS],"Jul") and Date.Month([End of Month])=7 then 1
else if Text.Contains([DUE MONTHS],"Aug") and Date.Month([End of Month])=8 then 1
else if Text.Contains([DUE MONTHS],"Sep") and Date.Month([End of Month])=9 then 1
else if Text.Contains([DUE MONTHS],"Oct") and Date.Month([End of Month])=10 then 1
else if Text.Contains([DUE MONTHS],"Nov") and Date.Month([End of Month])=11 then 1
else if Text.Contains([DUE MONTHS],"Dec") and Date.Month([End of Month])=12 then 1 else 0

11. Filter only Condition 2 equal =1

12. Data is already populate according to future month and due month.

I follow this youtube tutorial for this solution
https://www.youtube.com/watch?v=tnjzIq7jinQ&t=456s&pp=ygUrcG9wdWxhdGUgbW9udGggc3RhcnQgZGF0ZSBlbmQgZGF0ZSBwb3dlciBiaQ%3D%3D

Re: How to populate future value in date column according to due date column

Anonymous — Thu, 02 Nov 2023 04:18:39 GMT

Hi Anonymous ,

It's glad to hear that your problem has been resolved. And thanks for sharing your solution here. It will help the others in the community find the solution easily if they face the same problem as yours. Thank you.

Best Regards