Forum Discussion
Verification Of Logic
- Anonymous8 years ago
The best answer I can give is that "If it works, you've done it correctly". However correctly and efficiently are two different things.
Even in my own work, I could have done better and often through hard work i'm able to optimise my solutions to achieve the same result with either cleaner code, or with less processing time.
If you wanted to take this further, I would consider creating a date table. The date table to hold information on what you consider a week. You can also take "Today's Date" and therefore get information on how today would be handled. For example, what if you want to consider a week as being Monday to Sunday, rather than just last 7 days?
From here, you can use that data with Time Intelligence to help you get Power BI to calculate information you wish in a robust manner.
DAX will let you do some interesting things with the CALCULATE statement and with DATESINPERIOD. Here is an example I use:On Time Delivery Compliance = CALCULATE( [On Time %], DATESINPERIOD( Dates[Date], LASTDATE(Dates[Date]), -6, DAY ) ) + 0This particular measure calls another measure which calculates how many orders were released on time, divided by the total number of orders. This measure runs the calculation on the context of how I use this, by taking in the Date of the current context (i.e. Date on a Graph Axis) and calculates over the last 7 days. Now when i put this onto a graph, i tell the graph that it is only allowed to use Sunday dates. The result is a graph, week to week, of my On-time percentages, where a Week is considered Monday to Sunday. If i wanted Tuesday to Monday, all i need to do is change my 'Day of the Week' constraint to be the Monday. This works because my date Axis on the graph is from my date table, and my date table has a column for Day of the Week.
Hopefully this gives you some ideas you can work with.
The best answer I can give is that "If it works, you've done it correctly". However correctly and efficiently are two different things.
Even in my own work, I could have done better and often through hard work i'm able to optimise my solutions to achieve the same result with either cleaner code, or with less processing time.
If you wanted to take this further, I would consider creating a date table. The date table to hold information on what you consider a week. You can also take "Today's Date" and therefore get information on how today would be handled. For example, what if you want to consider a week as being Monday to Sunday, rather than just last 7 days?
From here, you can use that data with Time Intelligence to help you get Power BI to calculate information you wish in a robust manner.
DAX will let you do some interesting things with the CALCULATE statement and with DATESINPERIOD. Here is an example I use:
On Time Delivery Compliance = CALCULATE(
[On Time %],
DATESINPERIOD(
Dates[Date],
LASTDATE(Dates[Date]),
-6,
DAY
)
) + 0This particular measure calls another measure which calculates how many orders were released on time, divided by the total number of orders. This measure runs the calculation on the context of how I use this, by taking in the Date of the current context (i.e. Date on a Graph Axis) and calculates over the last 7 days. Now when i put this onto a graph, i tell the graph that it is only allowed to use Sunday dates. The result is a graph, week to week, of my On-time percentages, where a Week is considered Monday to Sunday. If i wanted Tuesday to Monday, all i need to do is change my 'Day of the Week' constraint to be the Monday. This works because my date Axis on the graph is from my date table, and my date table has a column for Day of the Week.
Hopefully this gives you some ideas you can work with.
Thanks a lot, mate - and what you wrote does give me some ideas. Thanks for taking the time to respond.