Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

DAX formula help

I have some data like this (here only shows the first 20 rows):  This is raw data about each project year, each state, region, device has multiple events, and the value for each event is "Count" column.  

Project_yearStateAreaNameLocalNameDevice_IDCountEvent_Date_Time
2016STATE_ABaseline                      L.R. BOYLE PARK          D3303467/31/10 8:25 AM
2016STATE_ABaseline                      L.R. HINDMAN             D9309098/23/09 6:40 AM
2016STATE_ABaseline                      L.R. HINDMAN             D93020551/12/13 1:54 PM
2016STATE_ABaseline                      L.R. HINDMAN             D93011947/3/17 3:48 AM
2016STATE_ABaseline                      L.R. EAST                F3401949/27/09 10:44 AM
2016STATE_ABaseline                      L.R. INDUSTRIAL          F5207912/19/09 9:19 AM
2016STATE_ABaseline                      L.R. INDUSTRIAL          F5207311/22/10 10:24 PM
2016STATE_ABaseline                      L.R. INDUSTRIAL          F520735/30/17 1:48 AM
2016STATE_ABaseline                      L.R. INDUSTRIAL          F5207311/3/17 4:32 PM
2016STATE_ABlytheville                   ARMOREL                  1001C2010/23/09 8:59 AM
2016STATE_ABlytheville                   ARMOREL                  1001C1828/21/10 9:48 PM
2016STATE_ABlytheville                   ARMOREL                  1001C1798/26/12 6:58 AM
2016STATE_ABlytheville                   BLYTHEVILLE I-55         10422124/7/15 11:35 AM
2016STATE_ABlytheville                   BLYTHEVILLE I-55         10422281/22/16 1:57 AM
2016STATE_ABlytheville                   BLYTHEVILLE ELM ST.      3415510/1/13 6:32 PM
2016STATE_ABlytheville                   BLYTHEVILLE ELM ST.      343672/13/16 4:53 AM
2016STATE_AHarrisburg                    NEWPORT                  N44610593/5/09 1:58 PM
2016STATE_AHarrisburg                    NEWPORT                  N44610517/21/09 8:23 AM
2016STATE_AHarrisburg                    NEWPORT                  N4464008/7/18 7:29 PM
2016STATE_AHarrisburg                    HARRISBURG               P80914762/25/10 8:46 PM

 

At the end of each month, we sum(count) the past 12 month then divided by 12 to get the 12 month rolling average,  in some month there is no event happening thus there will be no sum(count), however we still want to include it in the calculation. Here are my two calculation:

Rolling12MonTotal = CALCULATE(SUM('SAMPLE DATA'[Count]) 

        ,CALCULATETABLE('Calendar',DATESBETWEEN('Calendar'[Date],EDATE(min('Calendar'[Date]),-11),MAX('Calendar'[Date]))

        )       

    )

  

Rolling Average = if(ISBLANK(SUM('SAMPLE DATA'[Count])),BLANK(),divide('SAMPLE DATA'[Rolling12MonTotal], 12))

Rolling Average2 = divide('SAMPLE DATA'[Rolling12MonTotal], 12)

 

Question 1: As you can see, if I choose to graph "Rolling Average2" over date(year,month), then there is a problem that it plots the highlited 2019 all months data which are invalid, cos right now it is only 2019 Jan. If I choose to plot "Rolling Average", it is good to see all >2019Jan data points gone, but it creates new problem that some data points will be missing due to no data for that month, also those month data is not showing in tooltip.  For example these red highlighted numbers are missing in powerBI:

 

 

Question 2: I would like to create a measure to show only the last month data, for example, right now is 2019-01-12,  I would like to create  sum(2018-1-1 to 2018-12-31) / 12, which  the value is 507 (you can see this value from my above tooltip of graph "rolling average2"). How to create this measure? 

 

The pbix file can be found here:
https://drive.google.com/open?id=10xVkLYQ4c2TOI2rJEMyvC78vfYq7gAtJ

 

Thank you very much!

 

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    Hi,

     

    For Question1, try this rolling average measure

     

    =if(ISBLANK(MIN('Calendar'[Date])),BLANK(),divide('SAMPLE DATA'[Rolling12MonTotal], 12))

     

    Hope this helps.

6 Replies

  • LivioLanzo's avatar
    LivioLanzo
    Solution Sage
    7 years ago

    Hello Anonymous

     

    I have uploaded a file for you here: https://1drv.ms/u/s!AiiWkkwHZChHj1SpwTfrfT-hkjXD

     

    let me know if this is what you're after

     

    I have applied a much needed star schema and then used this measure:

     

    Rolling 12M Avg = 
IF(
    MAX( 'Calendar'[Month Year Sequence] ) > 12,
    IF(
        HASONEVALUE( 'Calendar'[Month Year Number] ),
        CALCULATE(
            DIVIDE(
                SUMX( 
                    SUMMARIZE( 'Calendar', 'Calendar'[Month Year Number], 'Calendar'[Month Year Name] ),
                    CALCULATE( SUM( Data[Count] ) )
                ),
                12
            ),
            FILTER(
                ALL( 'Calendar'[Month Year Sequence] ),
                AND( 
                    'Calendar'[Month Year Sequence] < MAX( 'Calendar'[Month Year Sequence] ),
                    'Calendar'[Month Year Sequence] >= MAX( 'Calendar'[Month Year Sequence] ) - 12
                )
            ),
            ALL( 'Calendar' )
        )
    )
)
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 years ago

    Hi,

     

    For Question1, try this rolling average measure

     

    =if(ISBLANK(MIN('Calendar'[Date])),BLANK(),divide('SAMPLE DATA'[Rolling12MonTotal], 12))

     

    Hope this helps.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi  Ashish_Mathur, thank you! It works for my question 1.

      Any suggestion for question 2? Thanks!

      • LivioLanzo's avatar
        LivioLanzo
        Solution Sage
        7 years ago

        I believe the answer is in my file to get the average of the previous 12 months