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
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
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.