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
kegoosse
Helper I
Helper I

Create a running sum which also shows values when a column dimension doesn't exist in a given year

Hi everyone, 

 

I have a Fact-tabel (called 'Fact') where I need to count the rows as a running sum (for 5 years) and a date tabel ('Date') to use the time sensing-DAX.

I want to calculate the running sum for the number of rows in the Fact-tabel, sorted for a single column/dimension in this tabel (called 'Fact'[Fac_cluster]).

 

The measure RunningSum is calculated as follows:

 

RunningSum = 
VAR NumOfYears = 5
VAR LastCurrentDate =
    MAX ( 'Date'[Date] )
VAR Period =
    DATESINPERIOD ( 'Date'[Date], LastCurrentDate, - NumOfYears, YEAR )
VAR Result =
    CALCULATE (
        SUMX(
            VALUES ( 'Date'[Year] ),
            COUNTROWS ( 'Fact' )
        ),
        Period
    )
VAR FirstDateInPeriod = MINX ( Period, 'Date'[Date] )
VAR LastDateWithSales = MAX ( 'Fact'[Start date] )
RETURN
    IF ( FirstDateInPeriod <= LastDateWithSales, Result )

 

 

This works as long as the dimension 'Fact'[Fac_cluster] exists for a given year, but gives a blank result when the dimension is non-existing.

For example: RC doesn't have any rows in the year 2019, but I stil want to count the running sum. Should be 4 for RC in 2019 in the table below but the measure RunningSum returns a blank in the matrix visual.

kegoosse_0-1633949276139.png

 

Does anyone know what adaptation I have to make to the measure?

 

Thanks a lot

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@kegoosse , Can you try a simpler version, also make a sure the year is from the date table

 

Cumm = CALCULATE(countries(Fact) ,filter(allselected(date),date[date] <=max(date[Date])))

View solution in original post

2 REPLIES 2
kegoosse
Helper I
Helper I

@amitchandak Thank you, simplifying the formula was the right idea.
Finally ended up with:

RunningSum = CALCULATE(
    COUNTROWS('Fact'),
    filter(
        ALLEXCEPT('Date','Date'[Year]),
        'Date'[Date] <= max('Date'[Date]) && 'Date'[Date] >= DATE(YEAR(max('Date'[Date]))-5,MONTH(max('Date'[Date])),DAY(max('Date'[Date])))
    )
)
amitchandak
Super User
Super User

@kegoosse , Can you try a simpler version, also make a sure the year is from the date table

 

Cumm = CALCULATE(countries(Fact) ,filter(allselected(date),date[date] <=max(date[Date])))

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.

Top Solution Authors