Forum Discussion

mschneider54's avatar
mschneider54
New Member
5 months ago
Solved

Error on Running Total with a Date Table

For context here I am using PowerBI Service. 

 

I am trying to get a Running Total for a year over the course of months. I have a table with my gift data (Amount, Date, etc) which I connected to data table. I have a column for Running Total on my gift data table that is the following:

Running total =
   VAR LastVisibleDate =
   TODAY()
RETURN
CALCULATE (
    SUM('Gift Data'[AmountReceived]),
    FILTER (
        ALL ('Date Table' ),
        'Date Table'[Date] <= LastVisibleDate
    ))

This calulatation is not working. It resets every month. 
Troublshooting I've already done:

  • My date table is all unique values
  • There are no null values on my date table
  • My gift data Date field has a match for every value on my date table
  • All of date columns are confirmed Date Only in Query Editor
  • I've tried ALL and ALLSELECTED

I am running into the issue that I want my relationship to run Date Table > Gift Table. However, Power BI keeps reverting to the Gift Table > Date Table.  
Please help!

 
Need Help
  • Natarajan_M's avatar
    Natarajan_M
    5 months ago

    Hi mschneider54 , 

    Based on the above problem statement I have recreated the scenario,
    In your dax you are resetting the month value thats causing the issue
    I hope this is what you are looking for .


    Visual :

    Gift Data :


    Date :


    Mark it as date table, Month name sort by month id .

    Data model: 

    Dax:

    1) using the YTD function:

    Running Total YTD = CALCULATE(SUM('Gift Data'[AmountReceived]), DATESYTD('Date Table'[Date]))


    2) using the max date funtion : 

    Running Total YTD by max function = 
CALCULATE(
    SUM('Gift Data'[AmountReceived]),
    FILTER(
        ALL('Date Table'),
        'Date Table'[Date] <= MAX('Date Table'[Date])
        && YEAR('Date Table'[Date]) = YEAR(MAX('Date Table'[Date]))
    )
)

     

    Pbix file :
    Running Sum.pbix

    Thanks 
    If you found this helpful, please consider giving it a kudo and marking it as the accepted solution — it goes a long way in helping others facing the same issue.

    For more Power BI tips and discussions, let’s connect on LinkedIn:
    https://www.linkedin.com/in/natarajan-manivasagan

    Cheers!


4 Replies

  • Natarajan_M's avatar
    Natarajan_M
    Icon for Super User rankSuper User
    5 months ago

    Hi mschneider54 , 

    Based on the above problem statement I have recreated the scenario,
    In your dax you are resetting the month value thats causing the issue
    I hope this is what you are looking for .


    Visual :

    Gift Data :


    Date :


    Mark it as date table, Month name sort by month id .

    Data model: 

    Dax:

    1) using the YTD function:

    Running Total YTD = CALCULATE(SUM('Gift Data'[AmountReceived]), DATESYTD('Date Table'[Date]))


    2) using the max date funtion : 

    Running Total YTD by max function = 
CALCULATE(
    SUM('Gift Data'[AmountReceived]),
    FILTER(
        ALL('Date Table'),
        'Date Table'[Date] <= MAX('Date Table'[Date])
        && YEAR('Date Table'[Date]) = YEAR(MAX('Date Table'[Date]))
    )
)

     

    Pbix file :
    Running Sum.pbix

    Thanks 
    If you found this helpful, please consider giving it a kudo and marking it as the accepted solution — it goes a long way in helping others facing the same issue.

    For more Power BI tips and discussions, let’s connect on LinkedIn:
    https://www.linkedin.com/in/natarajan-manivasagan

    Cheers!


  • GilbertQ's avatar
    GilbertQ
    Icon for Super User rankSuper User
    5 months ago

    Hi mschneider54 

     

    The reason that this is resetting every month is because you are using a variable to determine when to do the less than, equal to and when the variable runs it is putting that as a single value. That does not evaluate as part of your calculate statements and that is why it is resetting. Rather change it back to using the Max value as with the example above.

  • v-echaithra's avatar
    v-echaithra
    Icon for Community Support rankCommunity Support
    5 months ago

    Hi mschneider54 ,

    We’d like to follow up regarding the recent concern. Kindly confirm whether the issue has been resolved, or if further assistance is still required. We are available to support you and are committed to helping you reach a resolution.

    Best Regards,
    Chaithra E.

  • v-echaithra's avatar
    v-echaithra
    Icon for Community Support rankCommunity Support
    5 months ago

    Hi mschneider54 ,

    Just following up to see if the Response provided by community members were helpful in addressing the issue. if the issue still persists Feel free to reach out if you need any further clarification or assistance.

     

    Best regards,
    Chaithra E.