Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
fyoung
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
Ashish_Mathur
Super User
Super User

Hi,

 

For Question1, try this rolling average measure

 

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

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

6 REPLIES 6
Ashish_Mathur
Super User
Super User

Hi,

 

For Question1, try this rolling average measure

 

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

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

Any suggestion for question 2? Thanks!

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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!  

@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

LivioLanzo
Solution Sage
Solution Sage

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!  

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.