Forum Discussion
Function DATESINPERIOD doesn't work properly
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.
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
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.