Forum Discussion
Help with data between two dates
- 6 years ago
hi Anonymous
You could try this simple way
step1:
Use CALENDARAUTO Function to create a dim date table as below:
Date = ADDCOLUMNS(CALENDARAUTO(),"YearMonthNo",YEAR([Date])*100+MONTH([Date]),"Year Month", YEAR ( [Date] )& " " & FORMAT ( [Date], "mmmm" ) )Step2:
Use this logic to create a new table
New table = FILTER ( GENERATE ( 'Table', SUMMARIZE ( 'Date', 'Date'[Year Month], 'Date'[YearMonthNo] ) ), [YearMonthNo] >= YEAR ( [Start Date] ) * 100+ MONTH ( [Start Date] ) && [YearMonthNo] <= YEAR ( [end date] ) * 100 + MONTH ( [end date] ) )Result:
and you could also have a look this post that similar to your case.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365
here is my sample pbix file, please try it.
Regards,
Lin
Hi Anonymous ,
Based on my understanding of your requirements, I think a clustered bar chart should be able to show this. If you could provide an example dataset, I may be able to help a bit more.
Thanks
Hi there. Thank you for your reply. I have included sample data below:
Project A
start date: 2/1/2020
end date: 4/30/2021
monthly hours: 238
Project B
start date: 2/1/2020
end date: 11/30/2020
monthly hours: 159
Total available manpower: 960 hours
- Anonymous6 years agoNot applicable
I would like to be able to see all allocated hours for a given calendar month
- Anonymous6 years agoNot applicable
Is this similar to what you are looking for?
- Anonymous6 years agoNot applicable
Looking to aggregate all consumed hours across all projects in a given month against available.