Referencing Part of a Calendar Tablein a Moving Average Measure

[daniwill882]

Hi,

I'm currently working on a report to bring together all of our main datasets. This includes a page per dataset and then one to combine relevant data. I've created a calendar table to link the date element of all datasets together. The issue i'm having is with the moving average measure for one dataset, this is because there is a year's less data. All other datasets start on 04/01/2015 while this one only goes back to 04/01/2016. This is causing the April 2016 moving average to include previous months as if the value is 0.

The DAX i'm using for moving average is:

Confirm Moving Average = IF(COUNTROWS(VALUES('Calendar'[MonthNumber]))=1,CALCULATE([Count of Confirm Records]/COUNTROWS(VALUES('Calendar'[MonthNumber])),DATESBETWEEN('Calendar'[Date],FIRSTDATE(PARALLELPERIOD('Calendar'[Date],-2,MONTH)),LASTDATE(PARALLELPERIOD('Calendar'[Date],0,MONTH))),ALL('Calendar')))

My question is, is there anything I can add into the DAX so that it only includes data from 04/01/2016 rather than the whole calendar dates column.

I'm relatively new to Power BI so any advice would be greatly appreciated.

[Eric_Zhang · Accepted solution]

@daniwill882

What if you add a filter to the DAX? For further questions, please post some sample data(in plain text or file, we  can't copy from a snapshot) and expected output.

Confirm Moving Average =
IF (
    COUNTROWS ( VALUES ( 'Calendar'[MonthNumber] ) ) = 1,
    CALCULATE (
        [Count of Confirm Records] / COUNTROWS ( VALUES ( 'Calendar'[MonthNumber] ) ),
        DATESBETWEEN (
            'Calendar'[Date],
            FIRSTDATE ( PARALLELPERIOD ( 'Calendar'[Date], -2, MONTH ) ),
            LASTDATE ( PARALLELPERIOD ( 'Calendar'[Date], 0, MONTH ) )
        ),
        FILTER ( 'Calendar', 'Calendar'[Date] >= DATE( 2016, 4, 1 ) )
    )
)

[daniwill882]

Thank you, this works perfectly. I will do so in future posts, thanks for the advice.