cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kegoosse
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.

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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
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!

View solution in original post

2 REPLIES 2
kegoosse
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])))
    )
)
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])))

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
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!

View solution in original post

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

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.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors