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 avera...
Need Help
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

       

       

       

       

       

       

    • 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

       

       

       

       

       

       

      • DaveC123's avatar
        DaveC123
        Frequent Visitor
        9 years ago

        This is a great formula.  So now the 1 moves as the days data gets filled in.  But, I am stuck trying to still have that days data captured for use on a chart or as a "new measure".  The chart value shows "1".  I do not know how to write DAX, obviously, but was thinking something like this in order to create a "new measure": ?

         

              Todays SO = If('PowerBI Data Table'[Current Day],1,then('PowerBI Data Table'[Sales Orders] Column D)  

         

        I was hoping this would pickup that cell which has todays Sales Order number.  In the earlier table; 51