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
Anonymous
Not applicable

Add all the COGS by product for previous year

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 numberdocdateitem numberext. cost (FROM DB)quantityunit cost COGS
123410/27/2018abcde50.150174,592
123510/28/2018efghi1002200.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

1 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.

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

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
Super User
Super User

@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
Not applicable

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.

Anonymous
Not applicable

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

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.