Forum Discussion

OhLookAnError's avatar
OhLookAnError
Icon for Helper I rankHelper I
2 years ago

Rolling sum between relative dates?

Hello! I am trying to get a rolling sum between two dates. Normally I would use something like the statement below to just get for example the last 365 days:   Revenue Prior 365 Days= Calculate(SUM...
some_bih's avatar
some_bih
Icon for Community Champion rankCommunity Champion
2 years ago

Hi OhLookAnError please describe "how" you model you inputs for this wanted output, like do you use slicer of you just want measure depending on ...is 365 fixed all time or this is just year...if possible share model to better understand your model

OhLookAnError's avatar
OhLookAnError
Icon for Helper I rankHelper I
2 years ago

Hi, it is just going in a table. So for example, the first column would show the sum of the last 90 days and the next column would show the sum of the 90 days before that. 

  • some_bih's avatar
    some_bih
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Hi OhLookAnError  possible solution as measure

    Running sum test =
    CALCULATE (
    SUM(tblSummary[Revenue]),
    DATESINPERIOD (
    'Date'[Date], --this column must be Calendar / Date table in Power BI
    MAX ( 'Date'[Date] ),--this column must be Calendar / Date table in Power BI
    -1, --change as needed
    YEAR --period could Day, Month, Quarter, Year
    )
    )

    • OhLookAnError's avatar
      OhLookAnError
      Icon for Helper I rankHelper I
      2 years ago

      I got this below to work but is there a way to do it in months instead of days?

       

      PreviousPeriod = Calculate(SUM('tbl'[Revenue]),
              DATESBETWEEN('Calendar'[Date],
              MAX('Calendar'[Date]) - 180,
              MAX('Calendar'[Date]) -90))
  • Dangar332's avatar
    Dangar332
    Icon for Resident Rockstar rankResident Rockstar
    2 years ago

    Hi, OhLookAnError 

    OhLookAnError 

     

     

    try below 

    just adjust your column name

    rolling sum for 90 days

     

    365 Days =
Calculate(
    SUM(tblSummary[Revenue]),
      tblSummary[ReportDate]> max(tblSummary[ReportDate])-90 &&
            tblSummary[ReportDate]<=max(tblSummary[ReportDate]
          )

     

    rolling sum for 365 days

    365 Days =
Calculate(
    SUM(tblSummary[Revenue]),
      tblSummary[ReportDate]> max(tblSummary[ReportDate])-365 &&
            tblSummary[ReportDate]<=max(tblSummary[ReportDate]
          )