Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Ok so I have a problem I've been trying to solve for a while now. I have a few tables that I am using. The first is from a database that a third party created and it is what I use to get Invoice data from. In our ERP system, each item is given an item number for identification, but item numbers can be reused. In the database I use, this is accounted for by giving each item a separate product id to account for different versions of the item. I then have reports that record the item number, but since there is no product id in those reports, I'm having a hard time pairing the data and getting the item that was used at the time of purchase. Here is a sample of the data and what I have currently done (Data is fake):
From Database:
Product ID | Item Number | Description | LastDateInvoiced |
215678 | 00001 | Hot Dog | 8/1/2020 |
215679 | 00002 | Buns | 8/1/2020 |
215680 | 00001 | Hot Dog Jumbo | 9/18/2021 |
215681 | 00002 | Buns 8 Pack | 9/19/2021 |
From Report:
Date Returned: | Item Number |
6/7/2020 | 00001 |
7/31/2020 | 00002 |
2/1/2021 | 00001 |
How can I get it that the right description will fill in based on the Item Number and the date returned? Anyone have any thoughts?
Solved! Go to Solution.
Hey @PBCIT ,
I use this DAX statement to create a calculated column inside the report table:
Column =
var ItemNumber = 'report'[Item Number]
var DateReturned = 'report'[Date Returned]
var previousDate = MAXX(FILTER( SUMMARIZE( 'database' , database[Item Number] , database[LastDateInvoiced] ) , 'database'[Item Number] = 'database'[Item Number] && 'database'[LastDateInvoiced] < DateReturned ) , database[LastDateInvoiced] )
var checkPreVDate = IF( ISBLANK( previousDate ) , DateReturned , previousDate + 1)
var nextDate = MINX(FILTER( SUMMARIZE( 'database' , database[Item Number] , database[LastDateInvoiced] ) , 'database'[Item Number] = 'database'[Item Number] && 'database'[LastDateInvoiced] >= DateReturned ) , database[LastDateInvoiced] )
var _item = MAXX( FILTER( 'database' , 'database'[Item Number] = ItemNumber && ( 'database'[LastDateInvoiced] >= checkPreVDate && database[LastDateInvoiced] <= nextDate ) ) ,[Description] )
return
_item
What this statement does (or at least should do) is the following: determine the timeframe when an item was used based on the date returned column in the report table. These dates are stored in the variables checkPreviousDate and nextDate. Then these dates are used to filter for the description.
Based on the sample data you provided the report will look like this:
Please be aware, that this kind of calculation can become slow, depending on the size of the database table and the number of versions, this is due to the fact that SSAS Tabular, the underlying database engine of the Power BI data model does not know the data type sequence.
You may have to consider creating the item/description columns outside of Power BI and use a relational database in between (some kind of data warehouse).
Hopefully, this provides what you are looking for.
Regards,
Tom
Hi @PBCIT ,
Use the following dax to create a new column:
describution =
VAR maxdate =
CALCULATE (
MAX ( 'Table'[LastDateInvoiced] ),
FILTER (
ALL ( 'Table' ),
'Table'[LastDateInvoiced] > 'Table (2)'[Date Returned:]
&& 'Table'[Item Number] = 'Table (2)'[Item Number]
)
)
RETURN
CALCULATE (
MAX ( 'Table'[Description] ),
FILTER (
ALL ( 'Table' ),
'Table'[Item Number] = 'Table (2)'[Item Number]
&& 'Table'[LastDateInvoiced] = maxdate
)
)
Output result:
Did I answer your question? Mark my post as a solution!
Best Regards
Lucien
Hey @PBCIT ,
I use this DAX statement to create a calculated column inside the report table:
Column =
var ItemNumber = 'report'[Item Number]
var DateReturned = 'report'[Date Returned]
var previousDate = MAXX(FILTER( SUMMARIZE( 'database' , database[Item Number] , database[LastDateInvoiced] ) , 'database'[Item Number] = 'database'[Item Number] && 'database'[LastDateInvoiced] < DateReturned ) , database[LastDateInvoiced] )
var checkPreVDate = IF( ISBLANK( previousDate ) , DateReturned , previousDate + 1)
var nextDate = MINX(FILTER( SUMMARIZE( 'database' , database[Item Number] , database[LastDateInvoiced] ) , 'database'[Item Number] = 'database'[Item Number] && 'database'[LastDateInvoiced] >= DateReturned ) , database[LastDateInvoiced] )
var _item = MAXX( FILTER( 'database' , 'database'[Item Number] = ItemNumber && ( 'database'[LastDateInvoiced] >= checkPreVDate && database[LastDateInvoiced] <= nextDate ) ) ,[Description] )
return
_item
What this statement does (or at least should do) is the following: determine the timeframe when an item was used based on the date returned column in the report table. These dates are stored in the variables checkPreviousDate and nextDate. Then these dates are used to filter for the description.
Based on the sample data you provided the report will look like this:
Please be aware, that this kind of calculation can become slow, depending on the size of the database table and the number of versions, this is due to the fact that SSAS Tabular, the underlying database engine of the Power BI data model does not know the data type sequence.
You may have to consider creating the item/description columns outside of Power BI and use a relational database in between (some kind of data warehouse).
Hopefully, this provides what you are looking for.
Regards,
Tom
User | Count |
---|---|
127 | |
108 | |
100 | |
63 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |