cancel
Showing results for
Did you mean:
Frequent Visitor

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.

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

Thanks a lot

1 ACCEPTED SOLUTION
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])))

Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
2 REPLIES 2
Frequent Visitor

@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])))
)
)``````
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])))

Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Announcements

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.