Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Chthonian
Helper III
Helper III

Time Intelligence Guru Needed

Hi Datanuts,

I am looking for some advice/guidance on the best way to achieve a reporting requirement I have.

Scenario:

I have a fact table with 3 date columns;

  1. Order Date – Date order record was created
  2. Status Date – Date of last order update – used primarily for cancelled/rejected orders
  3. Completed Date – Order delivered date

My company wants me to report the gross profit on the following:

  • Back Orders: Any order that were not completed in the ordered month i.e. Work In Progress
  • New Orders: Any orders in a given month that were completed in the same month
  • Cancelled Orders: Any orders new or back orders that were cancelled

We work on the last full calendar month, so as an example this month Jan 2020 we would be reporting on Dec 2019. The main issue and confusion I am having is calculating across all three dates in a single measure. E.g. Orders where the completed date is not empty (Completed) + orders where status is cancelled && status date is in the reporting month + created date is before the start of the reporting month = Gross Profit

Measures:

Opening Back Orders: Any orders created on or before first day of the reporting month that were still in progress. This could include Back orders that got completed in the reporting month, that were cancelled in the reporting month, or were still in progress at the end of the reporting month.

Back orders delivered: Any orders created on or before first day of the reporting month that were still in progress but were completed in the reporting month.

Back Orders Cancelled: Any orders created on or before first day of the reporting month that were still in progress but were cancelled in the reporting month.

New Orders (Created, Delivered, Cancelled): Any new orders created in the reporting month, that get delivered or cancelled in the reporting month.

Back Orders Carried Forward: All orders (Back Orders + New Orders) that are still in progress at the end of the reporting month.

My Questions:

  • What is the best way to set up my data model? 1 Date Table or 3 Date Tables (Ideally, I want to use just 1 date table to allow for filtering/slicing on a field using a “MMM-YYYY” column)
  • Is USERELATIONSHIP possible to use when you need to reference 3 separate date fields in the same measure?
  • Would table variables for each scenario be a better way forward to create my measures?

Happy to provide a PBIX/Data Example but I wanted to ask for advice and guidance so I can work it out with some pointers rather than look for a final solution, after all that is how one learns.

Thanks in advance,

David

 

 

1 ACCEPTED SOLUTION
d_gosbell
Super User
Super User


@Chthonian wrote:
  • What is the best way to set up my data model? 1 Date Table or 3 Date Tables (Ideally, I want to use just 1 date table to allow for filtering/slicing on a field using a “MMM-YYYY” column)

If you just want to use a single date slicer then using a single date table is the best approach. The main time I see multiple date tables used in a data model is in situations where people want to produce matrixes with things like Ordered Date on rows and Completed Date on columns.

 


  • Is USERELATIONSHIP possible to use when you need to reference 3 separate date fields in the same measure?

 


Yes, you would simply call CALCULATE( ...  USERELATIONSHIP() )  on any of the inactive relationships to evaluate an expression using the other relationships. 

 


  • Would table variables for each scenario be a better way forward to create my measures?

I'm not entirely sure what you are referring to here, but I suspect the answer is "no" as creating inactive relationships and using the CALCULATE( ... , USERELATIONSHIP(...)) pattern would be the preferred approach.

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

d_gosbell
Super User
Super User


@Chthonian wrote:
  • What is the best way to set up my data model? 1 Date Table or 3 Date Tables (Ideally, I want to use just 1 date table to allow for filtering/slicing on a field using a “MMM-YYYY” column)

If you just want to use a single date slicer then using a single date table is the best approach. The main time I see multiple date tables used in a data model is in situations where people want to produce matrixes with things like Ordered Date on rows and Completed Date on columns.

 


  • Is USERELATIONSHIP possible to use when you need to reference 3 separate date fields in the same measure?

 


Yes, you would simply call CALCULATE( ...  USERELATIONSHIP() )  on any of the inactive relationships to evaluate an expression using the other relationships. 

 


  • Would table variables for each scenario be a better way forward to create my measures?

I'm not entirely sure what you are referring to here, but I suspect the answer is "no" as creating inactive relationships and using the CALCULATE( ... , USERELATIONSHIP(...)) pattern would be the preferred approach.

Hi @d_gosbell ,

 

Thanks a million for taking the time to respond. I am struggling to get my head around the USERELATIONSHIP function for multiple dates, so please excuse my persistent questions. I am on a mission to get an understanding of this side of Time Intelligence.

 

So in an instance with USERELATIONSHIP, my scenario would be as follows.

Calculate Gross Profit where;
Order Date is less than the 1st Dec 2019,

AND ( Completed Date is IN Dec 2019,
OR Status Date is IN Dec 2019 )

 

I am just unsure how to structure such a calculation/formula to handle all three date columns.

 

Many thanks again,

David

 

So that specific example is too complex to do with USERELATIONSHIP. If you have relationships they will be combined using a logical AND. If you need to mix in the "less than" and OR conditions you would need to do this in a DAX expression, but the exact expression depends on your data model. 

 

You mentioned that you would be happy to provide an example data model. If you could do that it would be much easier to give a concrete example of how to structure this sort of calculation. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.