Forum Discussion

haleswd258's avatar
haleswd258
Regular Visitor
2 years ago
Solved

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 f...
Need Help
  • amitchandak's avatar
    amitchandak
    2 years ago

    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)))

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
2 years ago

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)))

haleswd258's avatar
haleswd258
Regular Visitor
2 years ago

Thanks amitchandak 
The calculation I ended up using was:

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