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 
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's avatar
    antarizana
    Frequent Visitor
    1 year ago

    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.