Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Daily Sales report recent report date

I need to do a daily sales dashboard from a bunch of excels in sharepoint and wish to show top 5 sales name and volume of recent date (the latest day). 

 

Also  I want to add a slide for option of whole list. 

May anyone help?

 

Thank you. 

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Anonymous 

     

    Here I create a set of sample:

    Then add a measure:

     

    MEASURE =
VAR _MaxDate =
    MAXX ( ALL ( SalesData ), 'SalesData'[Date] )
VAR _vtable =
    VAR _vtable =
        SUMMARIZE (
            FILTER ( ALLSELECTED ( SalesData ), 'SalesData'[Date] = _MaxDate ),
            'SalesData'[Date],
            'SalesData'[SalesRep],
            "TotalSales", SUM ( SalesData[SalesVolume] )
        )
    RETURN
        TOPN ( 5, _vtable, [TotalSales], DESC )
RETURN
    IF (
        SELECTEDVALUE ( SalesData[SalesRep] )
            IN SELECTCOLUMNS ( _vtable, [SalesRep] )
                && MAX ( 'SalesData'[Date] ) IN SELECTCOLUMNS ( _vtable, [Date] ),
        CALCULATE ( SUM ( SalesData[SalesVolume] ), 'SalesData'[Date] = _MaxDate )
    )

     

    The result is as follow:

     

     

     

    Best Regards

    Zhengdong Xu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • DataNinja777's avatar
    DataNinja777
    Icon for Super User rankSuper User
    1 year ago

    Hi Anonymous ,

     

    I assume your organization uses an ERP system, and I recommend downloading the sales transaction data over your analysis date range, which includes details such as:

    • Posting date
    • Customer ID
    • Transaction amount
    • Transaction quantity
    • Product ID

    By using the complete sales data that aligns with the financial statement’s net sales figures, you can ensure that your analysis is based on your company’s official data source (ERP) rather than patching up multiple Excel files in SharePoint that may have undergone manual modifications. This approach provides more reliable and accurate data for analysis.

     

    Best regards,

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Anonymous 

     

    Here I create a set of sample:

    Then add a measure:

     

    MEASURE =
VAR _MaxDate =
    MAXX ( ALL ( SalesData ), 'SalesData'[Date] )
VAR _vtable =
    VAR _vtable =
        SUMMARIZE (
            FILTER ( ALLSELECTED ( SalesData ), 'SalesData'[Date] = _MaxDate ),
            'SalesData'[Date],
            'SalesData'[SalesRep],
            "TotalSales", SUM ( SalesData[SalesVolume] )
        )
    RETURN
        TOPN ( 5, _vtable, [TotalSales], DESC )
RETURN
    IF (
        SELECTEDVALUE ( SalesData[SalesRep] )
            IN SELECTCOLUMNS ( _vtable, [SalesRep] )
                && MAX ( 'SalesData'[Date] ) IN SELECTCOLUMNS ( _vtable, [Date] ),
        CALCULATE ( SUM ( SalesData[SalesVolume] ), 'SalesData'[Date] = _MaxDate )
    )

     

    The result is as follow:

     

     

     

    Best Regards

    Zhengdong Xu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.