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
mds123
Frequent Visitor

How can I have my running total be filtered by a slicer?

The below dax function returns a running total for the sales table I use. I'm able to filter the value by a customer table, but not by the date table I use. My date table has a custom column called Periodnum( values 1–12), and each period is roughly 28 days, but not always.

 

When I filter by the period, the date in my table updates, but the measure does not update as intended. It shows the total running time since the beginning of my dataset. I think the issue has to do with my variable LastDateWithSales. I replaced the calculate with just the max date, but that does not filter the measure either. My goal is that if I filter to Period 2, the counter starts at 0, and then day 1 would have the sales from day 1, day 2 would have the sales from days 1 and 2, etc.

 

 

Running Invoice =
var selectedPeriod = [selected period]
var LastVisibleDate = MAX(Dates[Date])
var FirstVisibleDate = min(Dates[Date])
VAR LastDateWithSales =
    CALCULATE (
        MAX ( 'Invoices'[Invoice Date]),
        REMOVEFILTERS()    -- Use ALL ( Sales ) if REMOVEFILTERS () and ALL ()
                           -- are not available
    )


 VAR Result =
    IF (
        FirstVisibleDate <= LastDateWithSales,
        CALCULATE (
            SUM('Invoices'[Total Gross Sales]),
            'Dates'[Date] <= LastVisibleDate
                )
    )    


RETURN Result

 

1 ACCEPTED SOLUTION
mds123
Frequent Visitor

Solved it! I wasn't including the fiscal year and period in the result variable. Below is the updated code if it helps anyone else out.

Running Invoice 2 =
var selectedPeriod = [selected period]
var selectedYear = [selected fiscal year]
var LastVisibleDate = CALCULATE(MAX(Dates[Date]))
var FirstVisibleDate = min(Dates[Date])
VAR LastDateWithSales =
 MAX ( 'Invoices'[Invoice Date])


 VAR Result =
    IF (
        FirstVisibleDate <= LastDateWithSales,
        CALCULATE (
            SUM('Invoices'[Total Gross Sales]),
            'Dates'[Date] <= LastVisibleDate,
            'Dates'[PeriodNum] = selectedPeriod,
            Dates[FY] = selectedYear
                )
    )    


RETURN Result

View solution in original post

1 REPLY 1
mds123
Frequent Visitor

Solved it! I wasn't including the fiscal year and period in the result variable. Below is the updated code if it helps anyone else out.

Running Invoice 2 =
var selectedPeriod = [selected period]
var selectedYear = [selected fiscal year]
var LastVisibleDate = CALCULATE(MAX(Dates[Date]))
var FirstVisibleDate = min(Dates[Date])
VAR LastDateWithSales =
 MAX ( 'Invoices'[Invoice Date])


 VAR Result =
    IF (
        FirstVisibleDate <= LastDateWithSales,
        CALCULATE (
            SUM('Invoices'[Total Gross Sales]),
            'Dates'[Date] <= LastVisibleDate,
            'Dates'[PeriodNum] = selectedPeriod,
            Dates[FY] = selectedYear
                )
    )    


RETURN Result

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.