Forum Discussion

Saveshen13's avatar
Saveshen13
New Member
5 years ago
Solved

Water Usage Calculation by water type

Hi All, 

 

Please can you assist

I am pulling data from a sharepoint list that uses forms to populate it, into Power Bi, 

I need to record the diffrence in volume recorded by water type from previous dates

ie: Watertype "Borehole" Volume Recorded for 02 Feb 2020- 30 Jan 2020 and so forth 

the idea is to populate this into a line graph for water usage per day per type

 

 

 

 

Need Help
  • Fowmy's avatar
    Fowmy
    5 years ago

    Saveshen13 

    Add this as a new column which you can use to plot on the chart:

    Variance = 
VAR _Date = 
  MAXX(
        FILTER(
            Table7,
            Table7[Date Recorded] < EARLIER(Table7[Date Recorded]) && 
            Table7[Water Type] = EARLIER(Table7[Water Type])
        ),
        [Date Recorded]
    )
return
IF(
    not ISBLANK(_Date),
    [Volume] - 
MAXX(
    FILTER(
        Table7,
        Table7[Water Type] = EARLIER(Table7[Water Type]) &&
        Table7[Date Recorded] = _Date
    ),
    Table7[Volume]
)
)

    ________________________

    If my answer was helpful, please consider Accept it as the solution to help the other members find it

    Click on the Thumbs-Up icon if you like this reply 🙂

    YouTube  LinkedIn

     



     

2 Replies

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    5 years ago

    Saveshen13 

    Add this as a new column which you can use to plot on the chart:

    Variance = 
VAR _Date = 
  MAXX(
        FILTER(
            Table7,
            Table7[Date Recorded] < EARLIER(Table7[Date Recorded]) && 
            Table7[Water Type] = EARLIER(Table7[Water Type])
        ),
        [Date Recorded]
    )
return
IF(
    not ISBLANK(_Date),
    [Volume] - 
MAXX(
    FILTER(
        Table7,
        Table7[Water Type] = EARLIER(Table7[Water Type]) &&
        Table7[Date Recorded] = _Date
    ),
    Table7[Volume]
)
)

    ________________________

    If my answer was helpful, please consider Accept it as the solution to help the other members find it

    Click on the Thumbs-Up icon if you like this reply 🙂

    YouTube  LinkedIn