Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
  • 1.  CUL Sum of a dynamic date

    Posted Jan 18, 2022 10:12 PM
    I have a question about getting a cumulative sum but have the date being dynamic and also filtering it to only show data that is less than month(today())

    scenario: Within a 12 month static FY period. The data will show, If we've passed Dec it will show the sum of those dates with data in the past, Oct, Nov, Dec.
    The problem is I have to chop the data since future dates contain the forecast amount and the past dates contain the actuals. The actuals replace the forecast on a monthly basis.

    I can get it to show the amount for the individual dates in the past Oct, Nov, Dec as an individual amount but not a cumulative amount.

    formula:
    var current month = date(year(month(today()), month(today()), 1)
    calculate(sum(column[amount]), filter(all selected(dates), date < current month))

    how can I modify this to get those three months or the dynamic months cumulative sum.

    ------------------------------
    Christopher Angus
    Business Analyst
    ------------------------------


  • 2.  RE: CUL Sum of a dynamic date

    Posted Jan 20, 2022 03:45 AM

    Hi @Christopher Angus I guess what you are looking for is Dateadd

    Try:

     Measure = Calculate([amount], Dateadd (Dates[date],-3,MONTH))

    This above should work considering you have a date table.

    Regards​

    Sam Casey Bolade



    ------------------------------
    GSC Bolade
    RG2 0FZ
    ------------------------------



  • 3.  RE: CUL Sum of a dynamic date

    Posted Jan 20, 2022 12:57 PM

    Thank you for the reply. I should of also included that I have filter "Filters on all pages" set for the date of Oct 21 - Sep 22. I need to override this filter also to get Jul 21 - Aug 21. 

    thank you,



    ------------------------------
    Christopher Angus
    Business Analyst
    ------------------------------