Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hello,
I have a data table that uses pre-determined weeks to create invoice totals. For example, Week 1 of January is Jan 1.-Jan 6. Week 2 is Jan6.-Jan13th etc. I need to create a measure (using a live dataset) that says something like since week 1 is only 5 days, 5/31 = 16% of that month. It should look something like (note I don't currently have a field like "Dayrange"):
Invoice Weeks | Percent of Month | Dayrange | Invoice | ID |
1 | 16% | 5 | $123123.123 | 1 |
2 | 23% | 7 | $123.123 | 2 |
3 | 23% | 7 | $123123123123.123 | 3 |
Is there way to know start and end date of week. then
datediff([start_of_week],[end_of_week],day)/day(eomonth([end_of_week]))
I was trying to avoid that solution as I have no way of finding the start and end date for each week. Although week 1 runs from Jan 1. -Jan 5. that doesn't mean that we received invoices every day in that range. In my case I only have invoices on the 3rd of January making that a bad method to find the start/end date.
hi @Anonymous
For your case, you must define the dim date in advance, for example:
Week | Start Date | End Date |
Then use this to calculate the Dayrange.
Regards,
Lin
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
93 | |
85 | |
83 | |
75 | |
49 |
User | Count |
---|---|
142 | |
141 | |
110 | |
69 | |
55 |