Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Grouped cumulative figures based on time

Hi, 

 

Firstly, I am new to PBI and I don't know if this is possible. Any help would be most appreciated. 

 

I am trying to get groups of cumulative running totals based on time. At the moment I have time and rainfall, I need to get groups for 2hr, 4hr, 6hr, 12hr, 24hr and 48hr.  

 

I have included a full 24 hour data set, as I am testing on this, however you would be at say 1pm and then refresh the data to show hour rainfall till that point. You will then want to see over the last 2 hours how much rain has fallen, with the other groups coming in as data becomes available. This can run for a number of days, hence the bigger groups.

 

I don't think that my variations on the following are the right way to achieve what I am after:

Cumulative = CALCULATE(sum(Table1[Rainfall(mm/hour)]), (filter(all(Table1[Time]),table1[Time] <=MAX(Table1[Time]))))

 

Data example and desired out come:

TimeRainfall(mm/hour)2hr4hr6hr12hr24hr
01:000.6     
02:001.4     
03:001.42    
04:000.62.8    
05:00124   
06:0011.64.4   
07:001.2246  
08:001.22.23.86.6  
09:001.82.44.46.4  
10:001.635.26.8  
11:000.83.45.87.8  
12:000.62.45.47.6  
13:001.41.44.87.213.2 
14:001.424.47.414 
15:001.42.84.27.614 
16:000.42.84.87.214 
17:000.41.84.6613.8 
18:000.20.83.65.613.2 
19:000.40.62.45.212.4 
20:000.40.61.44.211.6 
21:000.20.81.43.210.8 
22:0010.61.229.2 
23:000.61.222.68.6 
24:00:000.81.62.22.88.421

 

Thanks again in advance for your time.

Need Help
  • danextian's avatar
    danextian
    4 years ago

    Hi Anonymous ,

     

    For this, I'd create a helper table - one that doesn't have any relationship with  your facts or dimension tables. This can be done through import or enter data method.

    I would then create this measure

    Rainfall total within hour window = 
VAR __window =
    SELECTEDVALUE ( 'Group'[Hours] )
VAR __window2 =
    SELECTEDVALUE ( Rainfall[Time] ) - __window - 1
RETURN
    SWITCH (
        TRUE,
        __window = 0, SUM ( Rainfall[Rainfall(mm/hour)] ),
        __window2 < 0, BLANK (),
        CALCULATE (
            SUM ( Rainfall[Rainfall(mm/hour)] ),
            FILTER (
                ALL ( Rainfall[Time] ),
                Rainfall[Time]
                    >= ( MAX ( Rainfall[Time] ) - __window )
                    && Rainfall[Time] < MAX ( Rainfall[Time] )
            )
        )
    )

    Sample result

    Sample pbix for your reference -https://drive.google.com/file/d/1lyxLLfQZ_iJzxuRRVx_RNx1SOYrY4Ntk/view?usp=sharing 

4 Replies

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    4 years ago

    Hi Anonymous ,

     

    For this, I'd create a helper table - one that doesn't have any relationship with  your facts or dimension tables. This can be done through import or enter data method.

    I would then create this measure

    Rainfall total within hour window = 
VAR __window =
    SELECTEDVALUE ( 'Group'[Hours] )
VAR __window2 =
    SELECTEDVALUE ( Rainfall[Time] ) - __window - 1
RETURN
    SWITCH (
        TRUE,
        __window = 0, SUM ( Rainfall[Rainfall(mm/hour)] ),
        __window2 < 0, BLANK (),
        CALCULATE (
            SUM ( Rainfall[Rainfall(mm/hour)] ),
            FILTER (
                ALL ( Rainfall[Time] ),
                Rainfall[Time]
                    >= ( MAX ( Rainfall[Time] ) - __window )
                    && Rainfall[Time] < MAX ( Rainfall[Time] )
            )
        )
    )

    Sample result

    Sample pbix for your reference -https://drive.google.com/file/d/1lyxLLfQZ_iJzxuRRVx_RNx1SOYrY4Ntk/view?usp=sharing 

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Danextian,

       

      Thank you very much for this, it is what I am looking for. The only thing that is causing issues is the time, in your output time is coming through as a whole number and we are dealing with time in time format. Is there away to amend the dax? I tried hour(__window) and event rinafall[time] -__window/24, but they don't seem to work.

       

      Thanks

      • danextian's avatar
        danextian
        Icon for Super User rankSuper User
        4 years ago

        Is your time column in time format or is it a datetime? Is your time by the hour or also in minutes and seconds? The reason I used whole number is for simplicity. Also there is no 24:00 - you meant 00:00?