Forum Discussion

wetscott's avatar
wetscott
Icon for Helper I rankHelper I
6 years ago
Solved

Rolling 12 Month Count within a single file, from a folder data source

Dispite trying to follow the various other solutions, nothing seems to work. I believe what I'm trying to do is a little unique compared to previous solutions, hence the new topic.   So I've got my...
Need Help
  • wetscott's avatar
    wetscott
    6 years ago

    Omg got it! Here's the formula for anyone else that comes across this.

     

    CALCULATE(
    COUNTA('Report'[Notice Number]),
    DATESBETWEEN(
    AxisDateTable[Date],
    SAMEPERIODLASTYEAR(
    LASTDATE(AxisDateTable[Date])
    ),
    LASTDATE(AxisDateTable[Date])
    )
    )
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi wetscott ,

 

Not sure if i understand you correctly, but you may check the below measure.

Measure = CALCULATE(SUM('Table'[Total This Month]),FILTER(ALL('Table'),FORMAT('Table'[yearmonth],"YYYYMM")>FORMAT(EDATE(SELECTEDVALUE('Table'[yearmonth]),-12),"YYYYMM")&&FORMAT('Table'[yearmonth],"YYYYMM")<=FORMAT(SELECTEDVALUE('Table'[yearmonth]),"YYYYMM")))

 

Best Regards,

Jay

 

Best regards,

Jay 

wetscott's avatar
wetscott
Icon for Helper I rankHelper I
6 years ago

I'm still struggling with this one. I think I need some variation of this?

 

Count of Notice Number total for MonthYear =

CALCULATE(

COUNTA('Report'[Notice Number]),
FILTER(
ALLSELECTED('Report'),
format('Report'[Lodged Date], "YYYYMM") < FORMAT(MAX('Report'[Lodged Date]), "YYYYMM")
&&
format('Report'[Lodged Date], "YYYYMM") >= format(EDATE('Report'[Lodged Date],-12), "YYYYMM")
)
)
 
But can't get it to display anything. And when it does, it's not even close.
My thinking is my measure needs to look at the Report table while repecting the filter on the [DataDate] column set by 'DataDateFilter'[MonthYear].
Then, for each month on my axis, it needs to count -12 months to current axis month.
 
I'm just struggling to get anything that's rolling though. Everything comes out running total 0 to current :S
  • wetscott's avatar
    wetscott
    Icon for Helper I rankHelper I
    6 years ago

    Omg got it! Here's the formula for anyone else that comes across this.

     

    CALCULATE(
    COUNTA('Report'[Notice Number]),
    DATESBETWEEN(
    AxisDateTable[Date],
    SAMEPERIODLASTYEAR(
    LASTDATE(AxisDateTable[Date])
    ),
    LASTDATE(AxisDateTable[Date])
    )
    )