Forum Discussion

antarizana's avatar
antarizana
Frequent Visitor
1 year ago
Solved

Function DATESINPERIOD doesn't work properly

In POWER BI, I have imported general ledger with account, amounts, and dates (the date is always the last day of the month). The amounts for income and expense accounts are cumulative-the data on the...
Need Help
  • v-csrikanth's avatar
    v-csrikanth
    1 year ago

    Hi antarizana 
    I really sorry for the confusion here is the refined DAX that will help you to resolve our issue.
    If you still need facing anu issues please share the sample data so that i can help you address your requiement.
    Updated DAX 
    Rolling12Months =
    VAR __SelectedDate = MAX('Date'[Date])
    VAR __StartDate = EDATE(__SelectedDate, -11) // includes 12 months
    RETURN
    CALCULATE(
    SUM(YourTable[Amount]),
    DATESBETWEEN(
    'Date'[Date],
    __StartDate,
    __SelectedDate
    )
    )

    If the above information is helpful, please give us Kudos and mark the response as Accepted as solution.
    Best Regards,
    Community Support Team _ C Srikanth.


v-csrikanth's avatar
v-csrikanth
Community Support
1 year ago

Hi antarizana 
Thanks to the community for the support so far. After some investigating, here’s a step-by-step solution that works correctly for cumulative data, like in my simplified example:

If your data has dates and cumulative values (e.g., 1 to 12, then resets), do the following:

  • Import your table with Date and Cumulative columns.
  • Create a calendar table using:

Calendar = CALENDAR(MIN('Table'[Date]), MAX('Table'[Date]))

Relate Calendar[Date] to your data table’s Date.

  • Create this measure to get the actual monthly value:

Monthly Amount =

VAR PrevCum =

    CALCULATE(

        MAX('Table'[Cumulative]),

        FILTER(ALL('Table'), 'Table'[Date] = EDATE(MAX('Table'[Date]), -1))

    )

RETURN

    MAX('Table'[Cumulative]) - COALESCE(PrevCum, 0)

  • Create this measure to calculate the rolling 12-month total:

Rolling 12M Amount =

VAR MaxDt = MAX('Calendar'[Date])

VAR StartDt = EDATE(MaxDt, -11)

RETURN

    CALCULATE(

        [Monthly Amount],

        FILTER(ALL('Calendar'), 'Calendar'[Date] >= StartDt && 'Calendar'[Date] <= MaxDt)

    )

  • Use Calendar[Date] on the X-axis and Rolling 12M Amount as the value in your visual.

This method correctly calculates the monthly values and gives a reliable rolling 12-month sum even when working from cumulative input data.

Best Regards,
Cheri Srikanth