Calculate data for last 3 months from the Month(Of Record Entry Date)

[Liaise]

Hi,

I'm pretty new to Power Bi and i have a table with records that i need to calculate the last 3 months of data from the month of the current record. So I can then display a rolling total by month that contains the sum of data for the last 3 months for each month.

EG

Date              Value

01/01/2018     10

21/01/2018      8

15/02/2018      15

11/04/2018      20

05/05/2018      15

01/06/2018      10

This would then need to appear in a visual like this:

JAN 18    FEB 18     MAR 18    APR 18     MAY 18     JUN 18

  18            33            33            33             35              50

Would anyone know how i can achieve this using DAX?

Thanks

[v-yulgu-msft · Accepted solution]

Hi @Liaise,

New a calculated table first:

calendar table =
ADDCOLUMNS (
    FILTER (
        CALENDAR ( DATE ( 2018, 1, 1 ), DATE ( 2018, 6, 30 ) ),
        DAY ( [Date] ) = 1
    ),
    "Amount", CALCULATE (
        SUM ( Test4[Value] ),
        YEAR ( Test4[Date] ) = YEAR ( EARLIER ( [Date] ) )
            && MONTH ( Test4[Date] ) = MONTH ( EARLIER ( [Date] ) )
    )
)

Add a calculated column to above new table:

Running Total =
CALCULATE (
    SUM ( 'calendar table'[Amount] ),
    FILTER (
        'calendar table',
        [Date] <= EARLIER ( [Date] )
            && MONTH ( [Date] )
                >= MONTH ( EARLIER ( [Date] ) ) - 2
            && YEAR ( [Date] ) = YEAR ( EARLIER ( [Date] ) )
    )
)

Format the [Date] field to "MMMM yyyy".

Use a Matrix to visualize data.

Best regards,

Yuliana Gu

[Liaise]

Hi @v-yulgu-msft

That is fantastic and works perfectly

I have one other issue if you could work it out. There is another filter i need added to get these values correct in the same format as before.

I hope i can explain this clearly enough

This is another example of the same data as before with another column added that we need to filter on as well. We only need the most recent Audit Value in the 3 month ranges. So for example the 3 month range from Jan18 - Mar18 would show Mar18 total as 23 as the Audit Name A is listed twice but we only need the most recent value. 

Also is there a way in the calendar table to use the start and end date range for the Date column using the first and last date range from the entries from Test4[Date]

Date              Value      Audit Name

01/01/2018     10              A

21/01/2018      8               B

15/02/2018      15             A 

11/04/2018      20             B

05/05/2018      15             C

01/06/2018      10             A

So the totals would then need to appear in a visual like this:

JAN 18    FEB 18     MAR 18    APR 18     MAY 18     JUN 18

  18            23            23            35             35              45

If the above can be achieved are the results able to be filtered with a slicer from different values from the table the source data is stored in? Eg the total values are made up of different clients.

Thank you for your help so far. Really appreciate it