Forum Discussion

Vinod_P's avatar
Vinod_P
Icon for Helper I rankHelper I
3 years ago

Supply vs demand

Below is the sameple data and looking for demand( Count of values in demand date column) & Supply (Count of values in the supply date column) , Looking at a view to compare both counts like 1-1 against the fixed jan to dec month on X-axis.

 

ID NUMBERPart numberCUSTOMERDEMAND DATESUPPLY DATE
11Vinod 17/08/202212/01/2023
21Vinod 17/08/202204/05/2023
31Vinod 17/08/202227/01/2023
41Vinod 17/08/202205/04/2023
52Vinod 01/02/202220/01/2023
62Vinod 01/02/202220/01/2023
72Vinod 01/02/202220/01/2023
82Vinod 01/02/202220/01/2023
92Vinod 01/02/202210/03/2023
102Vinod 01/02/202228/02/2023
112Vinod 01/02/202210/03/2023
122Vinod 01/02/202205/04/2023
132Vinod 27/04/202220/01/2023
141Vinod 21/10/202205/04/2023
151Vinod 21/10/202205/04/2023
161Vinod 21/10/202217/04/2023
171Vinod 21/10/202204/05/2023
181Vinod 21/10/202217/04/2023

 

Expected outcome

 

Previous month closing stock should be added to the current 

LOB = Current month (Supply-Demand) + Previous month (LOB) 

Need Help
Tips and Tricks

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    Vinod_P , You have to create cumulative here. Also because both are in same table you will have active/inactive join when joining with a common date table

     

    Assume demand date join is inactive when you join both dates with same date table

     

     

    CALCULATE(Count(Table[Supply Date]),filter(date,date[date] <=maxx(date,date[date]))) -

     

    CALCULATE(CALCULATE(Count(Table[Demand Date]),USERELATIONSHIP( Date[Date], Table[Demand Date] ))

    filter(date,date[date] <=maxx(date,date[date])))

     

     

    Date Table

    Date = Addcolumns(calendar(date(2012,01,01), date(2024,12,31) ), "Month no" , month([date])
    , "Year", year([date])
    , "Month Year", format([date],"mmm-yyyy")
    , "Month year sort", year([date])*100 + month([date])
    , "Qtr Year", format([date],"yyyy-\QQ")
    , "Qtr", quarter([date])
    , "Month",FORMAT([Date],"mmmm")
    , "Month sort", month([DAte])
    , "FY Year", if( Month(_max) <7 , year(_max)-1 ,year(_max))
    , "Is Today" ,if([Date]=TODAY(),"Today",[Date]&"")
    ,"Day of Year" , datediff(date(year([DAte]),1,1), [Date], day)+1
    , "Month Type", Switch( True(),
    eomonth([Date],0) = eomonth(Today(),-1),"Last Month" ,
    eomonth([Date],0)= eomonth(Today(),0),"This Month" ,
    Format([Date],"MMM-YYYY") )
    ,"Year Type" , Switch( True(),
    year([Date])= year(Today()),"This Year" ,
    year([Date])= year(Today())-1,"Last Year" ,
    Format([Date],"YYYY")
    )
    )

     

    refer for related concepts

    Active/Inactive date

    Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
    https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

     

    Power BI Inventory On Hand: https://youtu.be/nKbJ9Cpb-Aw

     

     

     

    • Vinod_P's avatar
      Vinod_P
      Icon for Helper I rankHelper I
      3 years ago

      Hi Amit, 

       

      Thanks for the quick response. 

       

      I managed to get supply & demand formauls

      Supply = if(CALCULATE(COUNT(Tracker[SEQ ID]), FILTER(Tracker,Tracker[EST. FORECAST DELIVERY]<=EOMONTH(max('Date'[Date]),0) && Tracker[EST. FORECAST DELIVERY]>EOMONTH(max('Date'[Date]),-1)))=BLANK(),0,CALCULATE(COUNT(Tracker[SEQ ID]), FILTER(Tracker,Tracker[EST. FORECAST DELIVERY]<=EOMONTH(max('Date'[Date]),0) && Tracker[EST. FORECAST DELIVERY]>EOMONTH(max('Date'[Date]),-1))))
       
      and 
       
      Demand = if(CALCULATE(COUNT(Tracker[SEQ ID]), FILTER(Tracker,Tracker[CUST REQ DATE]<=EOMONTH(max('Date'[Date]),0) && Tracker[CUST REQ DATE]>EOMONTH(max('Date'[Date]),-1)))=BLANK(),0,CALCULATE(COUNT(Tracker[SEQ ID]), FILTER(Tracker,Tracker[CUST REQ DATE]<=EOMONTH(max('Date'[Date]),0) && Tracker[CUST REQ DATE]>EOMONTH(max('Date'[Date]),-1))))
       
      and LOB = supply- demand 
       
      But I am getting struck at adding previous month closing stock to the LOB . Could you please help in formula for considering the previous month closing stock