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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ScottR-MT
Helper I
Helper I

Need to Alter DAX Running Total Quick Measure to Ignore Date Filter

Hi,

 

I created a Running Total Quick Measure by Month, which I am filtering to only the last 6 months.  When filtered, the totals begin with the total from the first month displayed, and not from the beginning.  I would like for the total on the first month start with the total from the beginning of time, and not from that month.  Is this possible?  Below is the formula from the Quick Measure and a screen shot of the table.

 

IB =
CALCULATE(
    DISTINCTCOUNT('Install Base'[SerialNo]),
    FILTER(
        CALCULATETABLE(
            SUMMARIZE('Calendar', 'Calendar'[Month No], 'Calendar'[Month-Year]),
            ALLSELECTED('Calendar')
        ),
        ISONORAFTER(
            'Calendar'[Month No], MAX('Calendar'[Month No]), DESC,
            'Calendar'[Month-Year], MAX('Calendar'[Month-Year]), DESC
        )
    )
)
 
In this table, the totals should be much higher.  
If I remove the date filter, they go back to the correct amounts. 
PBI Table.png
1 ACCEPTED SOLUTION

Hi,

 

You may download my PBI file from here.

 

Hope this helps.

 

Untitled.png


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

View solution in original post

10 REPLIES 10
v-yulgu-msft
Employee
Employee

Hi @ScottR-MT ,

 

Please create an extra measure, add it to Matrix.

New Measure =
SUMX ( ActualTableName, [IB] )

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi,

 

Thank you, but this did not work.

Let me clarify.  The running total formula works as it is supposed to when all of the data is unfiltered in the visual.  I only want to show the last 3 months, but when I do that, the running total starts at the earliest month shown in the visual after filtering.  I want the total of the earliest month to be the total from all months from the beginning.  If it works as I would like it to in the bottom table, Dec-18 should be 80, and then grow from there.  I have attached an example PBI file, and added the below screenshot to show the difference.

 

The top table is unfiltered, and the running total is correct.  In the bottom table, I filtered by the last 3 months, and as you can see, it does not give me the actual total from the beginning of time, but rather the total starts from the first month in the visual.

 

PBI Table 2.png

 

Any help would be greatly appreciated!

 

Download: PBI Running Total Example

 

Quick Measure:

Count of Serial Number running total in Install Month = 
CALCULATE(
	DISTINCTCOUNT('Data'[Serial Number]),
	FILTER(
		CALCULATETABLE(
			SUMMARIZE('Data', 'Data'[Month No], 'Data'[Install Month]),
			ALLSELECTED('Data')
		),
		ISONORAFTER(
			'Data'[Month No], MAX('Data'[Month No]), DESC,
			'Data'[Install Month], MAX('Data'[Install Month]), DESC
		)
	)
)

 

Hi,

 

You may download my PBI file from here.

 

Hope this helps.

 

Untitled.png


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

I need to bring this back up again.  While the formula provided worked well before, I am now having trouble with it again.  This time, the visual will not display the (Running) Total IB amount from the previous months when there is no value for the particular row.  It may be because I am using it with SUM instead of COUNT or DISTINCTCOUNT, but I don't know for sure.

 

Here is my formula, and below is my screenshot of the result:

# IB = 
        CALCULATE(SUM('Install Base'[Model Count]), 
        DATESBETWEEN('Install Base'[Inst_Date], 
        MINX(ALL('Install Base'[Inst_Date]), 'Install Base'[Inst_Date]), 
        MAX('Install Base'[Inst_Date])
        )
)

Running Totals.png

Hi,

To your visual, drag Year and Month from the Calendar Table.  In your formula, replace 'Install Base'[Inst_Date] with Calendar[Date]


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

Yes it did, thank you very much!

Now for the follow-up...

 

How can I show the regular totals for each line item, but with running totals for only the total line?

Hi,

I don't think that is possible - "running totals for only the total line".


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Ashish_Mathur
Super User
Super User

Hi,

 

Share some data and show the expected result.


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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.