Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.