Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Running Totals Issue

Hi,

 

I have imported an Excel table with data recorded periodically (usually weeks), as per screenshot 1.  Some weeks there are no values in the movement, but I still want to show the running total.  I have used the measure below to calculate the running totals, but the dates shown have lost the context of the original 'weekly' dates and are displaying data for every day.  Please note there is a sub-category (Funding Type) that is included in the data.

 

Is it possible to show the running totals just for the dates in the original data?

 

KH1983_0-1652197940462.png

 

10 REPLIES 10
Ashish_Mathur
Super User
Super User

Hi,

Does this measure work?

Running Total = if(isblank(SUM(Admissions[Bed Movement])),BLANK(),CALCULATE(SUM(Admissions[Bed Movement]),DATESYTD(Calendar[Date]))

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Thank you Ashish for your reply.  The solution below has resolved the intial issue, but I need to find a solution to generating running totals where the data is blank for that week.

Hi @Anonymous ,

 

I create a sample to have a test.

RicoZhou_0-1652427243225.png

Dates table:

Dates =
ADDCOLUMNS (
    CALENDAR ( MIN ( 'Admissions'[Date] ), MAX ( 'Admissions'[Date] ) ),
    "Weeknum", WEEKNUM ( [Date], 2 )
)

Relationship:

RicoZhou_1-1652427281895.png

Measure:

Running Total = 
VAR _SUM =
    SUM ( Admissions[Bed Movement] )
VAR _RUNNING_TOTAL =
    IF (
        ISBLANK ( _SUM ),
        BLANK (),
        CALCULATE ( SUM ( Admissions[Bed Movement] ), DATESYTD ( Dates[Date] ) )
    )
RETURN
    IF (
        ISINSCOPE ( Dates[Date] ),
        _RUNNING_TOTAL,
        IF ( ISINSCOPE ( Dates[Weeknum] ), IF ( ISBLANK ( _SUM ), 0, _RUNNING_TOTAL ) )
    )

Result is as below.

In week level, matrix will show 0 in week3, due to there is not value in week3. 

RicoZhou_3-1652427336923.png

In Date level:

RicoZhou_4-1652427402381.png

 

Best Regards,
Rico Zhou

 

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

 

 

 

Anonymous
Not applicable

Hi Rico,

 

Thank you for your response which solves the problem at 'total level' but not for the sub-categories.  With reference to below, I am aiming to show the sub-category running totals for each week so for example, I can the values of the different sub-categories that the 54 for the 3rd January, consists of. 

 

At the moment the running total only shows for those categories where there is a movement value for the week.  I assume if somehow, where there is no value, a zero can be recorded in that week, then this would prompt a running total to be displayed for that category.

 

Thank you again for your help.

KH1983_1-1652693554069.png

 

Kind regards,

 

Keith

 

 

Hi @Anonymous ,

 

I think the calculation is based on your data model. Please share a easy sample file without sensitive data. For example, you can share a sample file with data only on 2022/01/01, 2022/01/03. Then show me more a screenshot with the result you want for total and sub-categories levels.

You can tell me more details about the calculate logic about how to get the result. This will make it easier for me to find the solution.

 

Best Regards,
Rico Zhou

 

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

Anonymous
Not applicable

Hi Rico,

 

Thank you for the response.  Please find attached a file with sample data and the desired report.  Whilst I have managed to produce a running total, the total is for every calendar day, whereas I am aiming to produce a report for only those dates that appear in the Admissions table.

 

Thank you again for your help.

 

Kind regards,

 

KH1983_0-1652871877893.png

 

 

https://1drv.ms/u/s!AkUuPQnVuYsakWg9oovFqtg6Cv-A?e=N7vYj7

 

DataInsights
Super User
Super User

@Anonymous,

 

The first step is to create a date table. See link below.

 

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/ 

 

Once you have a date table, mark it as a date table, and create a relationship between the Dates and Admissions tables. Then create this measure:

 

Running Total =
CALCULATE (
    SUM ( Admissions[Bed Movement] ),
    FILTER ( ALLSELECTED ( Dates[Date] ), Dates[Date] <= MAX ( Dates[Date] ) )
)

 

In the visual, use fields from the Dates table.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

Thank you for replying.

 

I have created the date table and followed all the steps in the video and those you suggested, including using the new date table dates on the visual, but the matrix table still shows all dates, rather than just the dates on the admissions table.

 

KH1983_0-1652285087504.png

 

I was able to get it to work using the Admissions date:

 

Running Total = 
CALCULATE (
    SUM ( Admissions[Bed Movement] ),
    FILTER ( ALLSELECTED ( Admissions[Date] ), Admissions[Date] <= MAX ( Admissions[Date] ) )
)

 

In the visual, use Admissions[Date]:

 

DataInsights_0-1652305348817.png

I believe what was happening originally in your report is that the automatic (built-in) date table was displaying every calendar date in the visual, instead of just the admission dates. Switching to a custom date table essentially replicated this same behavior in the visual. Thus, the revised DAX references the Admissions[Date] column and not the custom date table. However, it's still important to have a custom date table in your data model: you can create a date slicer and filter on year, quarter, etc., and you can perform time intelligence calculations like YTD more easily.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

Yes, this worked to show the correct dates (thank you), however, the table only shows a running total when there is a bed movement value for that funding type/week.  I would like the running total to display for every funding type, every week, irrespective whether there has been any movement.  

 

I guess this is a separate issue, being that there is no row to even add a zero value to that week because there is no data in the table.

 

Thank you again for your help.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors