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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
haleswd258
Regular Visitor

Monthly Rolling Year Count

I am wanting to display a rolling year count by month in a table visual.
Jan2023 would count all records from Jan2022-Jan2023. 

Feb2023 would count all records from Feb2022-Feb2023.  

This is my forumula.  Not every elegant, I know.

_R12M Machines in Warranty = 
VAR _1stOfMonthYearAgo =
    DATE ( 
        YEAR ( MIN ( dimDate[Date] ) ) - 1, 
        MONTH ( MIN ( dimDate[Date] ) ), 
        1 
    )
VAR _R12MCount =
    CALCULATE (
        DISTINCTCOUNT ( MachineStats[_Model-Serial] ),
        FILTER (
            MachineStats,
            MachineStats[StartUpDate] >= _1stOfMonthYearAgo
                && MachineStats[_WarrantyEnd] >= MAX ( dimDate[Date] )
        )
    )
RETURN
    _R12MCount

These are the two tables that I am using.

haleswd258_0-1705499852639.png

When I use my formula, it counts only the records for the current month, not the past year.

haleswd258_1-1705500072687.png

Any help would be appreciated.

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@haleswd258 , Based on what I got , In example I am using net measure and DimDate is date 

 

You can use measures like

 

Rolling 13 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-13,MONTH))

 

Rolling 13= CALCULATE([Net], WINDOW(-12,REL, 0, REL, ADDCOLUMNS(ALLSELECTED('Date'[Month Year],'Date'[Month Year Sort] ),ORDERBY([Month Year Sort],asc)))

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@haleswd258 , Based on what I got , In example I am using net measure and DimDate is date 

 

You can use measures like

 

Rolling 13 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-13,MONTH))

 

Rolling 13= CALCULATE([Net], WINDOW(-12,REL, 0, REL, ADDCOLUMNS(ALLSELECTED('Date'[Month Year],'Date'[Month Year Sort] ),ORDERBY([Month Year Sort],asc)))

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Thanks @amitchandak 
The calculation I ended up using was:

    CALCULATE(
        DISTINCTCOUNT ( MachineStats[_Model-Serial] ),
        DATESINPERIOD(
            dimDate[Date],
            MAX(dimDate[Date]),
            -13,
            MONTH
        )
    )

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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