cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ScottR-MT Frequent Visitor
Frequent Visitor

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
5 REPLIES 5
Super User
Super User

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

Hi,

 

Share some data and show the expected result.

Community Support Team
Community Support Team

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

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.
ScottR-MT Frequent Visitor
Frequent Visitor

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

Hi,

 

Thank you, but this did not work.

ScottR-MT Frequent Visitor
Frequent Visitor

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

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

 

Super User
Super User

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

Hi,

 

You may download my PBI file from here.

 

Hope this helps.

 

Untitled.png