Forum Discussion

DaveC123's avatar
DaveC123
Frequent Visitor
9 years ago
Solved

New Measure = Today's data compared to the average

I have a spreadsheet that I update daily.  I need a "new measure" to capture today's data (the last data in the column) to be able to use it as a field on a visual in order to compare it to the average.  (Basically "how busy today is in relationship to the average workload).  The data are in columns.  Does "Lastnonblank" work for this?  I can't figure it out.  A sample of the datat is below:

 

Date	weekday	workday	Sales Orders    Case QTY
6/5	Mon	 1	 21	        487
6/6	Tue	 2	 12	        1599
6/7	Wed      3	 30	        1480
6/8	Thu      4	 41	        604
6/9	Fri	 5       38	        10209
6/12	Mon	 6	 51	        3043
6/13	Tue	 7		
				
		  Average	32	2904
				
Todays workload based on average = 59%	4.70%

So, today (Mon 6/12) we have 59% more orders to process than the average.

So, today (Mon 6/12) we have 4.7% more cases to process than the average.

 

I'm just creating a quick visual to allow the team to quickly see in the morning how busy they will be that day.

 

Thank you, DaveC123

 

Need Help

7 Replies

  • OpenDataLab's avatar
    OpenDataLab
    Helper II
    9 years ago


    You can try these calculations to get to the above.  I have included an averge overall, a running averge, calculating only those days average which are in the past (relative to the current date) and the %.  This shouldgive you and indication over time of how you are perfoming not  just today.  Here is a link to a demo workbook: Today Compared to Average

     

    Sales Order (Running Avg) Var % =
    CALCULATE (
        DIVIDE (
            SUM ( Data[Sales Orders] ) - [Sales Orders (Running Avg)],
            [Sales Orders (Running Avg)]
        )
    ) 

    Sales Orders (Average Overall) =
    CALCULATE ( AVERAGE ( [Sales Orders] ), ALL ( 'Data' ) ) 

    Sales Orders (Running Avg) =
    CALCULATE (
        AVERAGE ( [Sales Orders] ),
        FILTER ( ALL ( 'Data' ), 'Data'[Date] <= MAX ( 'Data'[Date] ) )
    )

     

    Let me know how you get on.

    • DaveC123's avatar
      DaveC123
      Frequent Visitor
      9 years ago

      Thank you and this will work, but as time goes on, the graph will loose a lot of its eye popping affect.  I will use this as another dashboard if someone wants to drill into the details more, but I am not expecting these guys to do this.  I am initially just trying to provide a very simple visual (pictures are worth a thousand words).   Below is not coming from the same data, as it does not work right, but gives an idea about what I am trying to display.  A barchart or any other graph works, but it would only be two bars. (avg and today)

       Almost an average day.

      I am looking to just click refresh and it captures the last day on data.  And I understand that the average wil adjust over time. 

      Thanks, DaveC123

       

       

      • OpenDataLab's avatar
        OpenDataLab
        Helper II
        9 years ago

        To do this you can create a calculated column like this:

         

        Current Day =
        IF ( 'Data'[Date] = CALCULATE ( MAX ( 'Data'[Date] ), ALL ( 'Data' ) ), 1, 0 )

         

         

         

        Then you can either filter the report using this column or filter the report.  When you add new data the flag will automatically update to the latest day.

         

        I have updated the link to sample dashboard