파워쿼리로 판매약속가능수량(Available-to-promise) 산출하기

Available-to-promise (ATP)는 현시점에서 판매가 가능한 수량을 의미합니다. 

지난 번 일자별 재고 수량을 만들듯이 측정값을 이용하여 산출이 가능합니다. 주문 수량을 (-) 로 입고/생산 수량을 (+)로 적용하여 일자별로 누적 수량을 구하는 측정값을 만들면 됩니다. 

하지만 이번에는 조금 다른 방식, 파워쿼리로 데이터를 늘려서 일자별 판매약속가능수량을 산출하는 데이터 모델을 만들어 보도록 하겠습니다. 

이 방법은 아래 블로그의 기능 소개 글을 참고하여 작성하였습니다. 

www.poweredsolutions.co/2019/07/23/fill-dates-between-dates-with-power-bi-power-query/

Fill dates between dates with Power BI / Power Query — Powered Solutions

먼저 필요한 데이터를 정의하는것 부터 시작해보죠. 특정 시점의 판매약속가능수량(ATP)는 미래의 특정 시점에 납품 가능한 재고의 수량을 의미 하니 당연히 현재 시점의 판매가능한 재고량이 필요합니다. 여기에 이미 주문이 완료되어 납품예정인 수량 정보가 필요하고, 입고 또는 생산 계획이 필요합니다. 

즉, 현재 재고를 기준으로 앞으로 판매될 양과 입고될 양을 일자별로 합산하면 되죠.

'현재고 - 판매계획 + 입고계획 = 납품가능수량'  이것을 일자별로 계산하면됩니다. 

위의 3가지 정보를 가지고 일자별 판매가능수량을 파워쿼리로 산출해봅시다. 

이 모델에서 판매주문에서 판매계획, 구매발주에서 입고계획을 추출한다는 전제로 진행합니다. 계획 시작일을 2020-11-30으로 하고 계획구간의 종료일을 2020-12-30로 하는 재고계획을 일자별로 만들어서 일자별로 납품가능한 수량을 산출하는 쿼리를 만드는 적이 목표 입니다.  

작업시작 

쿼리 만들기

일단 데이터는 간단하게 시작합니다. 2개의 제품을 가지고 작업합니다. 모두 다 파워쿼리로 생성합니다. 

모두 파워쿼리로 생성한 후 각 일자 데이터를 모두 날짜만 나오도록 데이터 형식을 변경합니다. 

그리고 오늘일자와 계획종료일을 쿼리로 만들어 줍니다. 

생성한 '오늘일자'도 날짜로 변형하고 목록으로 변환합니다.

아래와 같이 나오면 성공입니다. 

같은 방법으로 계획구간의 마지막일자를 의미하는 계획구간 도 만들어 줍니다. 

 

쿼리 변형 : 일자별로 데이터 늘려주기

다음은 현재고, 판매주문, 구매발주 쿼리를 각각 다음 형태로 만들어 줘야 합니다.

1) 현재고 >>> 계획구간내 일자별 재고

2) 판매주문 >>> 주문번호별 일자별 주문잔량과 납품완료량 

 

3) 구매발주 >>> 발주번호별 일자별 입고량 

 

재고와 구매발주는 하나의 쿼리로 가능했으나 판매주문은 2개의 쿼리로 만들어서 진행했습니다. 주문잔량과 납품완료량을 다 표시 하려니 어쩔 수 없네요. 

이번 포스팅 주제의 핵심이죠. 계산된 기간만큼 일자를 늘려서 데이터를 구성하는 방법입니다. 

우선 사용자 지정 열 추가로 리스트 열을 추가해줍니다. 

 

 

= {Number.From([현재고일자])..Number.From(계획구간)}

현재고 테이블은 현재고일자(현재일) 부터 계획구간의 종료일(2020-12-30)까지 데이터를 늘려야 하므로 시작과 종료일을 넣어주면 됩니다. 단 Number.From 으로 데이터 타입을 변경 해주어야 에러없이 계산이 가능합니다. 

'기간만들기'라는 이름으로 필드를 추가한 다음 확장하기를 통해서 아래와 같이 데이터를 늘려주면 됩니다. 

나머지 쿼리도 동일한 방식으로 데이터를 늘려주면 됩니다. 자세한 수식은 첨부된 파일을 참조 하시는 게 좋을 것 같네요. 너무 포스팅이 길어져서요.. ㅡㅡ; 

쿼리를 합치기 : 쿼리 추가를 사용해서 작성된 쿼리를 모두 하나의 쿼리로 합치기

 

쿼리추가로 4개의 쿼리를 합치게 되면 필드명을 기준으로 같은 필드는 필드끼리 정리되지만 필드명이 다를 경우에는 다른필드로 생성되며 빈값을 null 로 채워지게 됩니다. 수식을 넣어야 하기 때문에 null 을 '바꾸기' 기능으로 '0'으로 바꾸어 줍니다. 

이제 현재고수량과 주문잔량, 주문완료량, 입고수량을 합친 필드를 '사용자 지정 열' 기능으로 만들어 줍니다. 

그리고 이 쿼리를 아래 그림처럼 그룹화 하여 엑셀에 로드합니다. 

드디어 완성입니다. 

결과를 설명하자면 라벤더의 12월 13일에 판매가능한 수량은 39개입니다. 산출과정은

 > 현재 재고가 46개
 > 12월 13일까지 입고가 100개 
 > 계획기간내 주문으로 예약된 총 수량은 107개 
 = 46 + 100 - 107 = 39 

입니다. 즉 현재고와 입고예정수량을 (+) 로 출고(판매)수량인 주문수량을 (-) 로 하여 계산을 하고 이를 일자별로 출력하여 스케줄을 한눈에 파악할 수 있도록 만든거죠. 

여기에 예상 수요나 예상 발주를 더 입력하면 보다 편하게 미래 시점별로 판매가능수량을 예측 해볼 수 있겠네요.

파일 첨부합니다. 

파워쿼리_ATP 만들기_업로드용.xlsx

0.03MB

이 컨셉을 다른 업무에 적용해 보자면 어떤것들이 있을까요? 판매가능의 반대인 자재소요계획이나 매출채권과 매입채무와 현재 현금 잔고를 가지고 자금계획 편성도 가능하겠네요. 여러모로 활용도가 높네요.