Forum Discussion

ArchStanton's avatar
ArchStanton
Power Participant
3 years ago
Solved

Average Year To Date

Hi,

 

I have this measure that calculates how many closures there have been in the current Financial Year (beginning Apr) = 6333

 

 

Closed Cases YTD = CALCULATE(
    TOTALYTD(COUNT('Cases'[Case Number]),'Cases'[Resolution Date],"31/03"),
    'Cases'[statecode] = "Resolved")

 

 

I would like to calculate the monthly rolling average so the measure is automatically divided by the current FY Month Number

which is 11 (Feb) and next month it will automatically divide by 12.

 

Thanks

Need Help
  • ArchStanton's avatar
    ArchStanton
    3 years ago

    Thanks for this, the numbers I was getting with this variable were approximately half of what they should be.

    No matter, I've managed to divide YTD calculation with this at the end which work:

     

     

    Months Since FY = 
            DATEDIFF(DATE(2022,3,31),
            date(year(TODAY()),
            MONTH(today()),
            day(today())),MONTH)

     

     

    Thanks for helping!

2 Replies

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    hi ArchStanton 

    try like:

    VAR ClosedCasesYTD = 
    CALCULATE(
        TOTALYTD(
            COUNT(Cases[Case Number]),
            Cases[Resolution Date],
            "31/03"
        ),
        Cases[statecode] = "Resolved"
    )
    VAR _month= 
    MONTH(MAX (DateTable[Date]))
    VAR _months =
    IF(
        _month>3,
        _month-3,
        _month+9
    )
    RETURN
    DIVIDE(ClosedCasesYTD, _months)
    • ArchStanton's avatar
      ArchStanton
      Power Participant
      3 years ago

      Thanks for this, the numbers I was getting with this variable were approximately half of what they should be.

      No matter, I've managed to divide YTD calculation with this at the end which work:

       

       

      Months Since FY = 
            DATEDIFF(DATE(2022,3,31),
            date(year(TODAY()),
            MONTH(today()),
            day(today())),MONTH)

       

       

      Thanks for helping!