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.
I need to get a monthly sum for the next three years. However, there is an effective date which is the start date and an expiration date. I need the total to populate according to the date range.
This is the current data:
Part # Monthly Rate EFFDT EXPDT
01-02-1303 17.00 11/27/2018 --
01-02-3000 1,029.60 2/4/2019 5/4/2019
I would like to create a table that has a 12 month view and shows the monthly rates for the next three years. The first part number does not have an expiration date. The second part number has a range. Below is how I would like the table to look like when completed.
Part # Year Jan Feb March
01-02-1303 2019 17 17 17
2020 17 17 17
2021 17 17 17
01-02-3000 2019 0 1029.60 1029.60
2020 0 0 0
2021 0 0 0
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
Hi,
You may download my PBI file from here.
Hope this helps.
Hi Ashish,
Thank you so much for your help. The file worked and I am now getting the results. The only issue I am having is that the month will not show in order. I went through your file and repeated the steps but I am afraid I am missing something. This is the result after I applied the steps shown within your file. Please let me know how to show the months in order. Thanks again.
Hi,
If my reply helped, please mark it as Answer. Create a relationshipship from the Month column of the Calendar Table to the Month column of the Month Order Table. In the Calendar Table, write the RELATED() function. Click on any cell in the month column of the Calendar Table and go to Sort By > Month Order. The months in the visual will now appear properly.
Thank you !! It worked. I did not sort it which was causing the issue. Again, thank you so much for your help!
You are welcome.
I don't think your current data structure supports this. However, I think you can quickly create a date table with the three years you're interested in:
Take a look at these two Quick Measures as I think you want something like them.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365
Perhaps also:
See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |