cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
fyoung Frequent Visitor
Frequent Visitor

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)

 

2019 extra points.JPG

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:

excel data.png

 

 

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!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: DAX formula help

Hi,

 

For Question1, try this rolling average measure

 

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

 

Hope this helps.

6 REPLIES 6
LivioLanzo Super Contributor
Super Contributor

Re: DAX formula help

Hello @fyoung

 

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

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Super User
Super User

Re: DAX formula help

Hi,

 

For Question1, try this rolling average measure

 

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

 

Hope this helps.

fyoung Frequent Visitor
Frequent Visitor

Re: DAX formula help

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

Any suggestion for question 2? Thanks!

LivioLanzo Super Contributor
Super Contributor

Re: DAX formula help

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

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

fyoung Frequent Visitor
Frequent Visitor

Re: DAX formula help

@LivioLanzo, I am sorry I checked your file a couple of times and the number is not right.

 

BTW I already solved my question 2. Thank you very much for both your input. @LivioLanzo @Ashish_Mathur

Super User
Super User

Re: DAX formula help

You are welcome.

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 177 members 1,693 guests
Please welcome our newest community members: