Forum Discussion

morab's avatar
morab
Advocate I
5 years ago
Solved

Tertiary Sales - Retail

Hi,

Good Day & Happy New Year 2021!!!

 

I have to calculate tertiary outlet sales by date & by cumulative;

 

Conditions:

By Date Tertirary Sales = Opening Stock of the day + Sales of the day - Closing Stock of the day;
For example:Tertirary Sales of 2-Jan-20 = 10 + 2 - 7 = 5;

 

By Cumulaive Tertiary Sales = Opening stock of specific date range + Sales of specific date range - Closing stock of specific date range;
For example: Total Teritary Sales from 1-Jan-20 to 4-Jan-20 = 5 + 13 - 3 = 15;
- Also Can it be possible to see by date cumulative in a table also like
1-Jan-21 = 0; 2-Jan-21 = 5; 3-Jan-21= 5; 4-Jan-21= 15;

 

Report Generation Principle:

- By Date or by specific week date range;

- Specific/multiple SKU wise Selection;

- Specific Outlet Wise/ All Outlet

 

Thank you in advance ðŸ™‚

 

Below is the required tables:

 

Data Source 1 :

Stock Survey Report

   
DateOutlet CodeOutletSKUPcs
01-Jan-2010001Rahim Store200gm5
02-Jan-2010001Rahim Store200gm10
03-Jan-2010001Rahim Store200gm7
04-Jan-2010001Rahim Store200gm10
05-Jan-2010001Rahim Store200gm3

 

Data Source 2 : Sales Report   
DateOutlet CodeOutletSKUPcs
01-Jan-2010001Rahim Store200gm5
02-Jan-2010001Rahim Store200gm2
03-Jan-2010001Rahim Store200gm3
04-Jan-2010001Rahim Store200gm3

 

Desire Result : Tertiary Sales - By date   
DateOutlet CodeOutletSKUPcs
01-Jan-2010001Rahim Store200gm0
02-Jan-2010001Rahim Store200gm5
03-Jan-2010001Rahim Store200gm0
04-Jan-2010001Rahim Store200gm10

 

Result : Tertiary Sales - By Cumulative   
Date RangeOutlet CodeOutletSKUPcs
1st Jan to 4th Jan 202010001Rahim Store200gm15

 

 

Best Regards

 

 

lc_finance 

amitchandak;

Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi  morab ,

     

    As the data you provided at the first time didnt contain different Outlet code,Outlet and SKU in Sales table and stock table,I didnt consider about the difference,that's the reason why you saw the error.Modify the measure as below:

     

     

    By Date Tertirary Sales = 
var _OpeningStockoftheday=CALCULATE(SUM('Stock Survey Report1'[Pcs]),FILTER('Stock Survey Report1','Stock Survey Report1'[Date]=SELECTEDVALUE('Calendar'[Date])&&'Stock Survey Report1'[Outlet Code]=MAX('Stock Survey Report1'[Outlet Code])&&'Stock Survey Report1'[Outlet]=MAX('Stock Survey Report1'[Outlet])&&'Stock Survey Report1'[SKU]=MAX('Stock Survey Report1'[SKU])))
var _Salesoftheday=CALCULATE(SUM('Sales Report'[Pcs]),FILTER(ALL('Sales Report'),'Sales Report'[Date]=SELECTEDVALUE('Calendar'[Date])&&'Sales Report'[Outlet Code]=MAX('Stock Survey Report1'[Outlet Code])&&'Sales Report'[Outlet]=MAX('Stock Survey Report1'[Outlet])&&'Sales Report'[SKU]=MAX('Stock Survey Report1'[SKU])))
var _ClosingStockoftheday=CALCULATE(SUM('Stock Survey Report1'[Pcs]),FILTER(ALL('Stock Survey Report1'),'Stock Survey Report1'[Date]=SELECTEDVALUE('Calendar'[Date])+1&&'Stock Survey Report1'[Outlet Code]=MAX('Stock Survey Report1'[Outlet Code])&&'Stock Survey Report1'[Outlet]=MAX('Stock Survey Report1'[Outlet])&&'Stock Survey Report1'[SKU]=MAX('Stock Survey Report1'[SKU])))
Return
_OpeningStockoftheday+_Salesoftheday-_ClosingStockoftheday

     

     

    And you will see:

    In the measure above,you need to identify the Outlet code,Outlet and SKU where I use such as

     

     

    'Sales Report'[Outlet Code]=MAX('Stock Survey Report1'[Outlet Code])&&'Sales Report'[Outlet]=MAX('Stock Survey Report1'[Outlet])&&'Sales Report'[SKU]=MAX('Stock Survey Report1'[SKU])

     

     

    to make sure that the calculation would be excuted with the correct data.

     

     For the updated .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!

6 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    5 years ago

    Should Closing Stock for a day be the same as opening stock for the same day, or for the next day?

    • morab's avatar
      morab
      Advocate I
      5 years ago

      Closing stock for a day is the opening stock for next day.

      • v-kelly-msft's avatar
        v-kelly-msft
        Community Support
        5 years ago

        Hi morab ,

         

        First create a calendar dim table;

        Then create 2 measures as below:

         

        By Date Tertirary Sales = 
var _OpeningStockoftheday=CALCULATE(SUM('Stock Survey Report1'[Pcs]),FILTER('Stock Survey Report1','Stock Survey Report1'[Date]=SELECTEDVALUE('Calendar'[Date])))
var _Salesoftheday=CALCULATE(SUM('Sales Report'[Pcs]),FILTER(ALL('Sales Report'),'Sales Report'[Date]=SELECTEDVALUE('Calendar'[Date])))
var _ClosingStockoftheday=CALCULATE(SUM('Stock Survey Report1'[Pcs]),FILTER(ALL('Stock Survey Report1'),'Stock Survey Report1'[Date]=SELECTEDVALUE('Calendar'[Date])+1))
Return
_OpeningStockoftheday+_Salesoftheday-_ClosingStockoftheday

        Tertiary Sales - By Cumulative = SUMX(ALLSELECTED('Calendar'[Date]),'Stock Survey Report1'[By Date Tertirary Sales])

         

        And you will see:

        For the relalted .pbix file,pls see attached.

         

        Best Regards,
        Kelly

        Did I answer your question? Mark my post as a solution!

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    5 years ago

    Hi,

    In the second table, how did you arrive at 5 pcs as on Jan 2, 2021?  It should be -3 i.e. 5+2-10 = -3