Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi Datanuts,
I am looking for some advice/guidance on the best way to achieve a reporting requirement I have.
I have a fact table with 3 date columns;
My company wants me to report the gross profit on the following:
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
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:
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
Solved! Go to Solution.
@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.
Refer to this article this will help in few of your calculation
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
@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.
User | Count |
---|---|
128 | |
108 | |
99 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |