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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
DataSundowner
Helper II
Helper II

Running Total as of n Days Ago Measure Improvement

Hello everyone. I'm hoping to get some help on improving my DAX measures. I have a Dimension table of about 55k rows and a bunch of Fact tables. What I'm trying to do is to create measures and KPI from fact tables and summarize them for each row in the Dimension table. It's pretty straight forward, but what I need is running total of those measures as of today and running total of thsoe measures as of n days ago. Running totals as of today all run pretty fast. The issues I'm having are with the running totals as of n days ago. n is a numeric paramter that report user can change through a slicer. It's getting me what I want, but running a little bit slow (about 2-3 mins to get it return for all rows in the Dim table). One example of such DAX measure is as below. Is there any way to improve the efficiency? Any insight is appreciated. 

 

 

 

Count - Running Total As Of Days Ago = 
CALCULATE(
    COUNT(Fact1[ID]),
    USERELATIONSHIP(Fact1[DateID], DimDate[DateID]),
    
    FILTER(
        ALL(DimDate),
        DimDate[xDaysAgo]>=DaysAgo_Parameter[DaysAgo_Parameter Value]
    ),
    FILTER(
        ALLSELECTED(Fact1[DateID]),
        Fact1[DateID]<=MAX(DimDate[DateID])
    )
)

 

 

 

 

3 REPLIES 3
Greg_Deckler
Super User
Super User

@DataSundowner Try:

Count - Running Total As Of Days Ago = 
CALCULATE(
    COUNT(Fact1[ID]),
    USERELATIONSHIP(Fact1[DateID], DimDate[DateID]),
    DimDate[xDaysAgo]>=DaysAgo_Parameter[DaysAgo_Parameter Value],
    Fact1[DateID]<=MAX(DimDate[DateID])
)

If you don't require the USERELATIONSHIP you could try this method: Better Running Total - Microsoft Power BI Community


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Hi @Greg_Deckler Thank you for your response. I tried your method, but got the following error message. 

 

"A function 'PLACEHOLDER' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

@DataSundowner Any chance you can post sample data? Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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