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

    Unfortunately that Measure is getting Year to Date of what's displayed in the table only.

    What I'm trying to indicate at each month though is the rolling Year to Date total at that month.

    ie.

    at Aug20, total Sept20 until Aug20

    at Jul20, total Aug20 until Jul20

    at Jun20, total Jul20 until Jun20

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