Function DATESINPERIOD doesn't work properly

[antarizana]

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 28.2.2023 include January and February. The data on 31.3.2023. includes all three months of the year, and so on. I needed the monthly amount, so I created it using the IF function and PARALLELPERIOD function (if its January, take the amount for January, otherwise, its the cumulative amount minus the ParallePeriod amount.

Then, I wanted to create a rolling sum for the past 12 months, but DATESINPERIOD function is not working correctly. I reduce the sum to 2 months (instead of 12 months) to more easily pinpoint the issue, and I saw that January and February are incorrect, while the other months are calculated correctly. I'm asking for advice on how to resolve the issue and a brief explanation of where the problem arises.

[v-csrikanth]

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]

Hi @antarizana 
I sincerely appreciate the time and effort you have taken to resolve your issue and share your findings.
We kindly request you to mark your response as the Accept as a Solution that is helful for the other community members faces similar issue.

Thanks and Regards,
Cheri Srikanth 
Community Support Team

[antarizana]

@v-csrikanth 

Sorry for the late reply, but clearly there has been some misunderstanding.
I haven't solved the problem, and formulas you suggested don't work well. I asked you to try getting the desired result with a simple example...

Honestly, I think this is an unsolved problem, and I am giving up on creating the rolling sum.

Thank you to everyone who tried to help me.

Best regards.

[v-csrikanth]

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

[v-csrikanth]

Hi @antarizana 
To calculate the Rolling 12-month sum on monthly amounts instead of cumulative amounts, you need to aggregate monthly values first and then apply the rolling sum.
Measure that calculates the monthly total:
Monthly Amount =
SUMX(
VALUES('Kalendar'[YearMonth]), 
SUM('BB'[Iznos])
)

Modify your rolling sum to sum over the past 12 months' monthly values instead of individual daily values:
Rolling 12M Amount =
VAR MaxDate = MAX('Kalendar'[Date])
VAR StartDate = EDATE(MaxDate, -11) 
RETURN
CALCULATE(
[Monthly Amount], -- Uses the Monthly Amount measure
FILTER(
ALL('Kalendar'),
'Kalendar'[Date] >= StartDate &&
'Kalendar'[Date] <= MaxDate
)
)

Could you please try updating the above DAX to see if it meets your requirements?

If the above information helps you, please give us a Kudos and marked the reply Accept as a solution.

Thanks,
Cheri Srikanth

[antarizana]

Hi.

Many thanks for your efforts, but it still doesn't work.

Please try to import a simle Excel table into Power BI. You will create it in Excel in less than a minute. The first column will have dates like this:
31.1.2023
28.2.2023
31.3.2023
... and so on until 31.1.2025.

In the second column, put amounts:
1
2
3
... up to 12. and again... from 1 to 12...

This are cumulative amounts. Monthly amounts should bu such that January's monthly amount equals the cumulative amount for January, and for the other months, it should be the current month minus the previous one. This means that the monthly amounts would always be 1... and rolling 12.

Link this table to the calendar in PBI and try to create a rolling 12-month sum. You'll see that these formulas don't work... I practically have this in the model, but with different numbers and more columns. This way, it's easier to understand and control numbers.

As for compliments, I'm happy to give a like, heart, or whatever, because you're making an effort to help me. But these formulas haven't solved my problem yet.

I'm sending you a warm greeting and an attached screenshot from Excel.

[v-csrikanth]

Hi @antarizana 
Apologies for the inconvenience. Here is the updated code that correctly calculates the rolling sum as expected.
-------------------------------------------------------------------
Rolling 12M Amount =

VAR MaxDate = MAX('Kalendar'[Date])
VAR StartDate = EDATE(MaxDate, -11) -- Adjust to -1 for a 2-month rolling sum
RETURN
CALCULATE(
SUM('BB'[Iznos]),
FILTER(
ALL('Kalendar'),
'Kalendar'[Date] > StartDate &&
'Kalendar'[Date] <= MaxDate
)
)

-------------------------------------------------------------------

If the above information helps you, please give us a Kudos and marked the reply Accept as a Solution.

Thanks,
Cheri Srikanth

[antarizana]

Hi, again.
Thank you so much for being with me and trying to help.

The last formula creates a good rolling sum on the cumulative amounts, but I need the rolling sum on the monthly amounts (the last formula for the monthy amount is fine).

I tried to include formula for the monthly amount (you wrote for me) in the last formula for rolling, but its not working (maybe I made mistakes somewhere...).

So, if you have any ideas on how to do it...
It seems like we're close to the solution, but I still dont have it.

[v-csrikanth]

Hi @antarizana 
As mentioned the above screenshots,your logic depends on PARALLELPERIOD, which works on a date hierarchy but may not behave as expected for Jan and Feb.
Instead of PARALLELPERIOD, use CALCULATE with FILTER:
**************************************************************
Monthly Amount =
VAR PrevCumulative =
CALCULATE(
SUM('BB'[Iznos]),
FILTER(
ALL('Kalendar'),
'Kalendar'[Date] = EOMONTH(MAX('Kalendar'[Date]), -1)
)
)
RETURN
IF(
MONTH(MAX('Kalendar'[Date])) = 1,
SUM('BB'[Iznos]),
SUM('BB'[Iznos]) - PrevCumulative
)
**************************************************************
Replace DATESINPERIOD with a FILTER-based approach to ensure proper date context:

**************************************************************

Rolling 12M Amount =
CALCULATE(
[Monthly Amount],
FILTER(
ALL('Kalendar'),
'Kalendar'[Date] <= MAX('Kalendar'[Date]) &&
'Kalendar'[Date] > EOMONTH(MAX('Kalendar'[Date]), -12)
)
)

**************************************************************
Please verify applying the rolling 12M Amount formula and check if January/February behave as expected.
Feel free to reach out if you need any further assistance.

If the above information helps you, please give us a Kudos and marked the reply Accept as a Solution.

Thanks,
Cheri Srikanth

[antarizana]

Hi, here I am again. Thank you very much for your help, but I have additional question.

The first formula for calculating the monthly amount works well. However, the second formula for rolling sum is not correct.

Since my model is huge, I created a simple table in excel and imported it into Power BI. The table has a column with dates, the last day of each month from January 2023 to December 2024, and for the amount, I entered numbers from 1 to 12 for each month. I also created the calendar directly Power BI (to make sure it's correct). Then, I applied the formulas you gave me, and the montly formula is fine. For the rolling sum, I set it to 2 months, to better understand where the error occurs. The second formula - for the sum of two months adds the third months (instead calculating the rolling sum) and it creates an issue with February. Here's the screenshot.

[v-csrikanth]

Hi @antarizana 
Hello and welcome to the Microsoft Fabric Community.
Please consider trying the following workarounds that may help resolve your issue.

1) This approach ensures that January is handled correctly without any calculation errors.
**************************************************************
MonthlyAmount =
IF (
MONTH(MAX('Kalendar'[Date])) = 1,
[Cumulative],
[Cumulative] - CALCULATE([Cumulative], PARALLELPERIOD('Kalendar'[Date], -1, MONTH))
)
**************************************************************

2) If you're working with multiple accounts or dimensions, using ALLEXCEPT ensures proper filtering and context control.

**************************************************************

Rolling12MonthSum =
CALCULATE(
SUM('Table'[MonthlyAmount]),
DATESINPERIOD('Kalendar'[Date], MAX('Kalendar'[Date]), -12, MONTH),
ALLEXCEPT('Table', 'Table'[Account])
)

**************************************************************

If the above information helps you, please give us a Kudos and marked the reply Accept as a Solution.

Thanks,
Cheri Srikanth

[Akash_Varuna]

Hi , 
Ensure your monthly amounts are calculated in a measure, not a calculated column, as measures recalculate dynamically for the context (like time periods).

Use the corrected monthly amounts for the rolling sum, ensuring the measure evaluates the MonthlyAmount, not the cumulative Amount column
Define a measure to calculate MonthlyAmount dynamically using Parallelperiod
If this post helps please do give a kudos and accept this as a solution 
Thanks in Advance

[antarizana]

Dear people, thank you so much for the warm welcome and the help you are offering me (and badge too 🙂 . It's much easier now knowing that I'm not alone in this...

Since I am a Power BI user only recenty, it's obvious that I'm making some mistake, so it might be best to show you all four formulas I'm using.
Screenshot below.

I also tried the ALLEXCEPT formula, but it doesn't work properly.