Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I am still new to powerbi and know just enough to create simple visuals. I need some help with date functions.
I am stuck on creating a measure that will add the extended cost for each item number (tied to product line in product table) with a date that is within the last 365 days. I need it by item number and my current query brings back a sum (not correctly) in total. Ultimately, I want to calculate inventory turnover by product line, by item.
This is the data from the sales table.
Invoice number | docdate | item number | ext. cost (FROM DB) | quantity | unit cost | COGS |
1234 | 10/27/2018 | abcde | 50. | 1 | 50 | 174,592 |
1235 | 10/28/2018 | efghi | 100 | 2 | 200. | 174,592 |
The code I have been using (not correctly) for COGS TTM:
Any help would be greatly appreciated!
Thank you and have a great day!
Steph
Solved! Go to Solution.
Hi @Anonymous ,
You can try to remove all() function in the filters to check whether the result is correct. All() returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied.
COGS TTM =
VAR LasDate =
LASTDATE ( _Date[Date] )
RETURN
IF (
TODAY () >= FIRSTDATE ( _Date[Date] ),
CALCULATE (
[COGS],
_Date[Date],
DATESBETWEEN (
_Date[Date],
NEXTDAY ( SAMEPERIODLASTYEAR ( LasDate ) ),
LasDate
)
)
)
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
The calculation gives me the total cogs (all items) for ttm. I need it for each product. What am I doing wrong? It must be in the COGS measure which is:
@Anonymous , Your expected output is not very clear
if you rolling 1 year or 12 months data you should try a measure like this with date table
Rolling 12 = CALCULATE([COGS,DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))
Rolling 12 = CALCULATE([COGS,DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-1,Year))
I would like the result to be:
Item Number | COGS TTM
Hi @Anonymous ,
You can try to remove all() function in the filters to check whether the result is correct. All() returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied.
COGS TTM =
VAR LasDate =
LASTDATE ( _Date[Date] )
RETURN
IF (
TODAY () >= FIRSTDATE ( _Date[Date] ),
CALCULATE (
[COGS],
_Date[Date],
DATESBETWEEN (
_Date[Date],
NEXTDAY ( SAMEPERIODLASTYEAR ( LasDate ) ),
LasDate
)
)
)
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you. I was able to get it to work!
User | Count |
---|---|
124 | |
106 | |
99 | |
63 | |
59 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |