Monthly Rolling

[vincenttys]

Hi All,

I only started to use PowerBI and already stuck with what i currently working on and hopefully i can get the assistance i need from this community.

Please see below is the snapshot of the dataset that i am currently working on. The data is added daily.

04/04/2018	Hazard	4
04/04/2018	Incident	4
01/04/2018	Hazard	4
30/03/2018	Hazard	4
29/03/2018	Incident	4
28/03/2018	Fire	4
28/03/2018	Fire	4
28/03/2018	Collision	4
27/03/2018	Incident	4
27/03/2018	Near Miss	3

What i required to present is:

- Total type of event (Only Hazard and Incident) starting previous month (Example March 2018 and prior) as we are only half way into April 2018.

- Above need to be present in total 3 month rolling. (Example: For March 2018, i need the total from period between January2018 and March2018. For February 2018, i need the total from period between December2017 and February2018 etc).

Thanks in Advance.

Regards,

Vince

[v-jiascu-msft · Accepted solution]

Hi @vincenttys,

Please check out the demo here.

1. Create a new date table;

2. Establish relationships;

3. Create a measure.

Measure =
CALCULATE (
    SUM ( Table1[Value] ),
    DATESINPERIOD (
        'Calender'[Date],
        EOMONTH ( MIN ( 'Calender'[Date] ), -1 ),
        -3,
        MONTH
    )
)

Best Regards,

Dale

[v-jiascu-msft · Accepted solution]

Hi @vincenttys,

Can you mark the proper answer as a solution please?

Best Regards,

Dale

[v-jiascu-msft · Accepted solution]

Hi @vincenttys,

Please check out the demo here.

1. Create a new date table;

2. Establish relationships;

3. Create a measure.

Measure =
CALCULATE (
    SUM ( Table1[Value] ),
    DATESINPERIOD (
        'Calender'[Date],
        EOMONTH ( MIN ( 'Calender'[Date] ), -1 ),
        -3,
        MONTH
    )
)

Best Regards,

Dale