cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
Saurabh129
Frequent Visitor

Rolling Totals for the last month without Date context

I am trying to get the 12 month  Rolling Total Amount to show in a table where I dont have the date in the row context 

I have State or city dimention as row context
I just need the rolling totals for the last month  i.e.  Nov 2021 to Oct 2022

I need the sum of the total amounts for the last 12 months as one of the columns in the table below (column 6) 
irrespective of whatever date filters / slicers are picked 

Saurabh129_0-1668175312191.png


can someone please help with this

1 ACCEPTED SOLUTION
Daryl-Lynch-Bzy
Resident Rockstar
Resident Rockstar

Hi @Saurabh129 - I believe that you mean that you want the previous 12 months of sales based on Today date.  Do you have a calendar table?  If so the following should work:

Rolling Last 12 Month Sales Amount =
VAR _EndDate = EOMONTH( TODAY(), -1)
RETURN
CALCULATE (
    [Sales Amount],
    DATESINPERIOD (
        'Date'[Date],
        _EndDate,
        -1,
        YEAR
    )
)

View solution in original post

1 REPLY 1
Daryl-Lynch-Bzy
Resident Rockstar
Resident Rockstar

Hi @Saurabh129 - I believe that you mean that you want the previous 12 months of sales based on Today date.  Do you have a calendar table?  If so the following should work:

Rolling Last 12 Month Sales Amount =
VAR _EndDate = EOMONTH( TODAY(), -1)
RETURN
CALCULATE (
    [Sales Amount],
    DATESINPERIOD (
        'Date'[Date],
        _EndDate,
        -1,
        YEAR
    )
)

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors