[파워피벗] 누적측정값으로 일자별 재고 만들기

회사에서 ERP솔루션을 통해 자재 관리를 하고 있다면 모두 알고 있을 만한 내용입니다. 바로 현재고 관리입니다. 거의 대부분의 ERP 솔루션은 자재의 입출 거래를 관리하고 이를 실시간으로 요약하여 현재 시스템 재고를 산출합니다. 현재고 정보는 ERP솔루션의 자재관리에서 차지하는 비중이 매우 큽니다. 바로 MRP의 근간이 되기 때문이죠. 현재 보유한 재고에 대한 정보가 있어야 발주시점과 양을 산출할 수 있기 때문이죠. 얘기가 새는 것 같네요. 

현재고는 자재의 거래와 관련된 가장 중요한 정보입니다. 하지만 가끔은 현재 재고가 아닌 특정 시점의 재고량이 필요한 경우가 있습니다. 보통 ERP 솔루션에서는 제공되지 않는 기능이죠. 과거 특정일자의 재고는 얼마였는가? 오늘은 이것을 엑셀로 구성해보죠. 

▶문제 

자재코드/거래유형/거래일자 단위로 구성된 실적 데이터를 매일매일 자재 코드별 재고량을 구성하는 것이 목표. 

주의할 점은 매일매일 나와야 한다는 점. 즉, 거래가 없는 날도 데이터가 들어와야 한다는 것이다. 물론 파워 피벗 안 쓰고도 구성이 가능하다. 먼저 일자와 자재 코드를 먼저 작성한 후 실적의 일자와 자재 코드를 인수로 SUMIFS 함수로 값을 가져오고 덧셈식으로 구성하면 간단한 일이다. 

하지만 매번 해야하는 불편함과 데이터의 분석용 가치를 떨어뜨리는 고정된 서식으로 구성되는 한계를 극복하려면 역시 피벗 테이블로 표현되는 것이 BEST ~!! 

하지만 피벗 테이블로 아무리 돌려봐야 답이 안 나온다. 

우선 피벗테이블로는 누적합계 표현이 안된다. 둘째로 피벗테이블은 데이터에 있을 경우만 표현된다. 즉 거래가 없는 일자는 표현할 수 없다. 

1월 3일에는 거래가 없다. 하지만 일별 재고량에는 표현이 필요하다. 

그래서 파워피벗이 필요하다. 

참고 : 포스팅된 내용의 DAX 수식은 DAX Patten이라는 사이트에 기재된 수식으로 사용하였다. 배을 것이 많은 사이트임.. 짱~!!www.daxpatterns.com/cumulative-total/

Cumulative total – DAX Patterns

▶작업 

실적 테이블을 "데이터 모델에 추가"로 데이터 모델의 테이블로 추가한다. 데이터모델의 테이블명은 "fTrans"로 이름을 정하였다.

그리고 날짜 테이블을 새로 만든다. 

날짜 테이블을 만들게 되면 알아서 적정한 기간의 일자를 모두 데이터로 생성해 준다. 

날짜 테이블이 구성 된 모습

날짜 테이블의 일자(DATE) 필드와 일별 거래현황(fTrans)의 거래일자와 관계를 맺어준다. 

 

이제 측정값을 만들 차례다. 

먼저 '거래수량'을 만들어준다. 

그리고 오늘의 하이라이트인 '누적 수량'을 만들어준다. 

=VAR LastVisibleDate = MAX ( '달력'[일자] )
VAR FirstVisibleDate = MIN ( '달력'[일자] )
VAR LastDateWithSales = CALCULATE ( MAX ('fTrans'[거래일자]), ALL( 'fTrans' ) )
VAR Result = 
    IF ( 
        FirstVisibleDate <= LastDateWithSales,
        CALCULATE (
            [거래수량],
            '달력'[일자] <= LastVisibleDate )
    )
RETURN
    Result

▶결과

모든 일자가 피벗에 표현되고 일자별 재고량(누적 수량)이 산출되었다. 재고를 일자별로 모니터링하고 발주점이나 소비 리드타임을 산정할 때 매우 유용한 도구가 될 것 같다. 

[파워피벗] 누적측정값으로 일자별 재고 만들기.xlsx

0.38MB