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" colu...
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.

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