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 the min date of every account. Below is my formula, the PeriodDate[PeriodStartDate] is a calculated column that takes the inital start date and creates a new date on the aniversary every year to track what is being spent annually.

Period Amount =
CALCULATE(
    SUM(Amount]),
    DATESINPERIOD(dimdate[DATE],Min(PeriodDate[PeriodStartDate]),
TODAY()-Min(PeriodDate[PeriodStartDate]),DAY)
)
 
This is how my data displays in a PowerBI Table. The line is correctly calculating the period amount from the Period Start to today. But the total is off because when it does it for both it takes the min date of 6/1/2024 for both Accounts which is incorrect for Account 1A.
AccountPeriodStartDateInitial DatePeriod Amount
1A9/1/20249/1/2022100
2A6/1/20246/1/2020200
    
Total  500

 

Below here is the data it is pulling from. The only 1A amount I would want to pull is the 10/1/2024.

AccountDateAmount
1A7/1/2024100
1A8/1/2024100
1A10/1/2024100
2A7/1/2024100
2A8/1/2024100
   
Total 500

 

Any help or tips would be greatly appreciated!

 

Thank you!!

 

 

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.

2 Replies

  • 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]
)

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    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.