Calendars, multiple date fields, and sum with variable filter dates

Used for anything related to the new Power BI Desktop and Service tools
Posts: 1
Joined: Mon Jan 14, 2019 1:54 pm

Calendars, multiple date fields, and sum with variable filter dates

Postby TMP » Mon Jan 14, 2019 2:02 pm

I have a Calendar table created using power query in PowerBI as described in Matt Allington’s excellent webinar, then added a SQL query for my sales data. The sales data has dates for both order entry (DATE ENTERED) as well as order invoiced (INVOICED DATE). I linked the invoiced date to the date in the calendar table, which works well for most everything I’m trying to do, but there’s one plot I’d like to generate that I’m really struggling to put together, and I’m hoping someone could nudge me in the right direction.

One thing I noticed is it seems like the behavior of DATE ENTERED is different than the INVOICED DATA when I reference the Calendar [date] in a table. I guess part of the issue is that INVOICED DATE is blank() if the order hasn’t invoiced yet, and maybe since Calendar [date] is linked to INVOICED DATE then I’m only seeing orders when INVOICED DATE has a value. But if I link to DATE ENTERED then the Calendar[date] is referencing the day the order was entered and not the day it invoiced, which doesn’t give me accurate measures for past performance.

The other problem, which might be related, is I’m trying to see the value of open sales orders over time – orders which have been entered but not invoiced. So for a specific date, what’s the sum of sales order values where DATE ENTERED>=Calendar[date]>=INVOICED DATE. I can filter this down properly in a table using visual filters and specifying a single date in both the date entered (on or after date filter) and invoiced date (on or before OR if blank filter), but what I’d really like to do is plot out the actual open orders total per day over a period of time to see how open orders is behaving.

Is there a problem with having two date fields? Anybody have suggestions on how to see the value of my open order book over time?

Thanks in advance.

Posts: 1015
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Calendars, multiple date fields, and sum with variable filter dates

Postby MattAllington » Mon Jan 14, 2019 3:36 pm

you will definitely get different behaviour from the date column that has the relationship. Once you join the invoice table to the calendar table, you should use the date columns from the calendar table in your reports. This implies (as you have suggested) that the calendar table is actually an "invoice date" calendar table. It is not a "date entered" calendar table. There are at least 3 approaches. This raises a very important point being that the decisions you make at this time will impact the way your report works and in many cases, "how hard" it is to write the DAX and how easy it is to solve problems. You need to think through the options and make a decision - it is normally not clear cut.

1. Load a second calendar table. Rename the tables so it is clear which is which. Also rename the columns so you can tell the difference between Invoice Month and Date Entered Month.
2. Create an inactive relationship between the calendar date and the date entered. Write bespoke measures that use CALCULATE and USERELATIONSHIP to make it work.
3. You could pre-process the date data before loading to meet your requirements. I can't tell you how to do this because it depends on your requirements, but for illustration purposes you could create 2 new columns in the invoice table using Power Query on load (remove the 2 date columns you already have after the fact). Column1 Transaction Date, Column2, Transaction Type. Write a custom column that takes the invoice date if it is invoiced already and call the Transaction Type "Invoice". If the Invoice date is blank, then load the date entered and call the transaction type "Order Date" or similar. Then you have a single date column and you can report using the transaction type column as needed. The downside is you lose the date entered, but if that is an issue you can simply keep that column (don't delete it) or even calculate a "lead time" column that says how many days prior to invoicing was the entered date. There are lots of options.

Regarding your other problem, yes it is related. I suggest you choose the best approach above and then post back for advice on how to best solve this one. Regardless of your approach, you should be able to write a measure called "open orders" that handles the exact need and will just work.

I have an article on relationships here ... es-in-dax/
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

Return to “Power BI Desktop/Service”

Who is online

Users browsing this forum: No registered users and 1 guest