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
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

 

 

 

 

 

 

  • 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