Add all the COGS by product for previous year

[Anonymous]

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:

COGS TTM = VAR LasDate = LASTDATE(_Date[Date])

return IF(

TODAY() >= FIRSTDATE(_Date[Date]),

CALCULATE(

[COGS],

ALL(_Date[Date]),

DATESBETWEEN(

_Date[Date],

NEXTDAY(SAMEPERIODLASTYEAR(LasDate)),

LasDate)))

Any help would be greatly appreciated!  

Thank you and have a great day!

Steph

[Anonymous]

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:

COGS = SUMX(SOP30300_SalesTxnDetail,SOP30300_SalesTxnDetail[UNITCOST]*SOP30300_SalesTxnDetail[QUANTITY])

[amitchandak]

@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))

[Anonymous]

I would like the result to be:

Item Number   |  COGS TTM

[v-yingjl · Accepted 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.

[Anonymous]

Thank you. I was able to get it to work!