Forum Discussion

WimpeyRoss's avatar
WimpeyRoss
New Member
1 year ago
Solved

DAX Calculation from Minimum Date to Total Sums causing headache

Hello,   I have a data set where I am trying to calculate an accounts spend during a time period. In order to calculate it I need to use a Min([Date]) which when summing or totalling, then takes th...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi WimpeyRoss ,

    Based on the description, creating the calculated column periodStartDate.

    PeriodStartDate = CALCULATE(MIN(PeriodDate[Date]), ALLEXCEPT(PeriodDate, PeriodDate[Account]))

    Then, try using the following DAX formula to calculate the account spend during a time period.

    Period Amount = 
SUMX (
    VALUES (PeriodDate[Account]),
    CALCULATE (
        SUM (PeriodDate[Amount]),
        DATESINPERIOD (
            dimdate[DATE],
            MIN (PeriodDate[PeriodStartDate]),
            TODAY() - MIN (PeriodDate[PeriodStartDate]),
            DAY
        )
    )
)

    The result is shown below.

     

    Best Regards,

    Wisdom Wu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Bibiano_Geraldo's avatar
Bibiano_Geraldo
Icon for Super User rankSuper User
1 year ago

Hi WimpeyRoss ,

Please try the bellow DAX and let me know if its all ok:

Period Amount = 
SUMX(
    SUMMARIZE(
        'YourTable',
        'YourTable'[Account],
        "AccountPeriodAmount",
        CALCULATE(
            SUM('YourTable'[Amount]),
            DATESINPERIOD(
                dimdate[DATE], 
                MIN(PeriodDate[PeriodStartDate]),
                TODAY() - MIN(PeriodDate[PeriodStartDate]), 
                DAY
            )
        )
    ),
    [AccountPeriodAmount]
)