Forum Discussion

isidnis's avatar
isidnis
Regular Visitor
7 years ago

Calculate data from absolute measures

Hello,   I cannot find a solution for a probably very simple question. I have a table with an absolute measure of a utility counter with the timestamp when it was measured e.g The key is that ...
beginner
Dax Help
AlB's avatar
AlB
Icon for Community Champion rankCommunity Champion
7 years ago

Hi isidnis 

Probably the easiest is to create an additional calculated column with the day only (i.e., discarding the time) :

DayColumn = INT(Table1[Timestamp])

and make the new column type date. Then create a standard date table and you'll be able to set up many of the things you're describing easily. For instance,  average consumption per day:

1. Set Table1[DayColumn] in the rows of a matrix visual

2. Create a measure and place it on the matrix:

AverageMeasure = AVERAGE(Table1[Measure])
  • isidnis's avatar
    isidnis
    Regular Visitor
    7 years ago

    Thanks a lot for your answer, my problem is that the measure is an absolute value of a counter. So the average consumption per day would be (Value at the end of the day 1 - Value at the beginning of the day 1) +(Value at the end of the day 2 - Value at the beginning of the day 2)...... / (Number of days with data)

    • AlB's avatar
      AlB
      Icon for Community Champion rankCommunity Champion
      7 years ago

       

      It would help and save us time if you described in detail what you need from the beginning, like you've done now. You can then create a measure and place it in a card visual (still using the new calculated column described earlier):

       

      Measure2 =
AVERAGEX (
    DISTINCT ( Table1[DayColumn] ),
    VAR ValBeginDay_ =
        CALCULATE ( MIN ( Table1[Measure] ) )
    VAR ValEndDay_ =
        CALCULATE ( MAX ( Table1[Measure] ) )
    RETURN
        ValEndDay_ - ValBeginDay_
)

       

      • isidnis's avatar
        isidnis
        Regular Visitor
        7 years ago

        Sorry about the lack of info I am pretty new in all this area:)

         

        I am going to try to be more specific. The table I have has actually 3 columns deviceid:that identifies the utility counter, timestamp of the measure and absolute measure of the counter that is a number that goes up everytime a user consumes electricity.

        What I want to achieve, (your last formula didn´t work) is something like:

         

        I was thinking about having a column with differential values of each day, 

        DAY CONDUMPTION = MAX(Table[Measure])-(CALCULATE(MAX(Table[Measure]),PREVIOUSDAY(Table[DayColumn]))

        But it doesn´t work either.

        Sorry again for the misunderstanding