Verification Of Logic

[Budfudder]

I'm new to PowerBI and just want someone to verify that I'm taking the correct approach.

I have a DB table which contains a record for each order we receive. Management wants a report showing how many orders we received in the last week, how many between 1 and 2 weeks ago, how many between 2 and 3 weeks ago, how many between 3 and 4 weeks ago, and how many more than 4 weeks ago.

So I created a new column (call it DaysSinceOrder) and populated it with the DATEDIFF between the order date and NOW() in days. Then I created a new column for each of the 'buckets' and gave each a value of 1 if the row's DaysSinceOrder value is within the range of that bucket. So for the 'Last Week' bucket, it was:

IF (DaysSinceOrder <= 7, 1, 0)

For the 1-2 week bucket, it was:

If (DaysSinceOrder >7 && DaysSinceOrder <= 14, 1, 0)

And so forth.

Then I just dragged those columns to the visualization.

That works - I can verify the values it comes up with from another source. I just want to know if I'm doing it correctly or if I'm missing some much easier method.

The second question is - can I do much the same thing for the value of each order? Then the 'Last Week' bucket would be:

IF (DaysSinceOrder <= 7, OrderValue, 0)

and so forth.

Thanks in advance.

[Anonymous · Accepted solution]

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
    )
) + 0

This 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.

[Budfudder]

Thanks a lot, mate - and what you wrote does give me some ideas. Thanks for taking the time to respond.