cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
erikm
Frequent Visitor

Issue with displaying rolling totals

Hi,

I am trying to accomplish the following scenario:

  1. User selects one date (that is the end date of a selection)
  2. The beginning of the date range is automatically set to January 1st of the previous year
  3. The measure contains only +1 oder -1 values that need to be displayed in a graph as rolling totals.

In the workbook that I have uploaded here, I have prepared and drawn an example of how it should look like. I have tried several DAX statements to calculate the rolling total, but most of them either didn't show anything at all, or data from all years.

The dataset is based on the default WideWorldImporters database.

Thank you!

1 ACCEPTED SOLUTION
RicoZhou
Community Support
Community Support

Hi @erikm 

Try If function, I update the measure.

balance RT =
VAR _EndtDay =
    SELECTEDVALUE ( 'Calendar'[Date] )
VAR _StartDay =
    DATE ( SELECTEDVALUE ( 'Calendar'[YEAR] ) - 1, 01, 01 )
RETURN
    IF (
        MAX ( Movements[Year] ) * 100
            + MAX ( Movements[Month] )
            <= SELECTEDVALUE ( 'Calendar'[YEAR] ) * 100
                + SELECTEDVALUE ( 'Calendar'[Month] ),
        CALCULATE (
            SUM ( Movements[balance] ),
            FILTER (
                ALL ( Movements ),
                Movements[MeasureDate] >= _StartDay
                    && Movements[MeasureDate] <= _EndtDay
                    && Movements[Year] = MAX ( Movements[Year] )
                    && Movements[Month] <= MAX ( Movements[Month] )
            )
        ),
        BLANK ()
    )

Result is as below.

1.png

Best Regards,

Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

View solution in original post

5 REPLIES 5
amitchandak
Super User IV
Super User IV

@erikm , Assume you have selected a date in the slicer

 

Try a measure like

 

measure =
Var _Min1 = MAXX(allselected('Date'), 'Date'[Date])
Var _Min1 = date(year(min1)-1, 1,1)
Var _Max = MAXX(allselected('Date'), 'Date'[Date])
return CALCULATE(SUM(Table1[Value]), filter(Table1, Table1[date]=_max && Table1[date]=_min))
// or
return CALCULATE(SUM(Table1[Value]), filter(all(Date), Date[date]=_max && Date[date]=_min))

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Hi @amitchandak ,

thank you for your suggestion. It feels like I'm almost there 🙂 The graph is displayed correctly with the months filtered, the only thing is that it displayed the separate monthly values and not a rolling total. I've tried to create an extra measure based on this new one, but then all of a sudden all years are displayed again. Would you have a suggestion for this?
Thank you!

RicoZhou
Community Support
Community Support

Hi @erikm 

In dax, catching date values from automatic time intelligence hierachy will make your code logic complex and use date in same table to filter value will make result incorrect due to the impactions between columns in same table.

Here I suggest you to turn off automatic time intelligence, and add an unrelated calendar table by dax.

 

Calendar = ADDCOLUMNS(CALENDARAUTO(),"YEAR",YEAR([Date]),"Month",MONTH([Date]),"Day",DAY([Date]),"MonthName",FORMAT([Date],"MMMM"))

 

Add Year, Month, MonthName columns into Movements Table by Dax.

 

Year = YEAR(Movements[MeasureDate]) 
Month = MONTH(Movements[MeasureDate])
MonthName = FORMAT(Movements[MeasureDate],"MMM")

 

Measure:

 

balance RT = 
VAR _EndtDay = SELECTEDVALUE('Calendar'[Date])
VAR _StartDay = DATE(SELECTEDVALUE('Calendar'[YEAR])-1,01,01)
RETURN
CALCULATE(SUM(Movements[balance]),FILTER(ALL(Movements),Movements[MeasureDate]>=_StartDay&&Movements[MeasureDate]<=_EndtDay&&Movements[Year] = MAX(Movements[Year])&&Movements[Month]<=MAX(Movements[Month])))

 

Result is as below.

1.png

Best Regards,

Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

 

erikm
Frequent Visitor

Hi @RicoZhou 

thank you for the suggestion, that certainly helps me further! One small thing that I still couldn't figure out that you might have a quick solution for. In your above screenshot, you selected a day somwhere in May 2021. How would I hide the line that goes horizontal? In other words, when I select a date in May 2021, I would like the line in the graph to stop after May and not display the same figures for the other months up until today.

Thank you!

Best regards,

Erik 

RicoZhou
Community Support
Community Support

Hi @erikm 

Try If function, I update the measure.

balance RT =
VAR _EndtDay =
    SELECTEDVALUE ( 'Calendar'[Date] )
VAR _StartDay =
    DATE ( SELECTEDVALUE ( 'Calendar'[YEAR] ) - 1, 01, 01 )
RETURN
    IF (
        MAX ( Movements[Year] ) * 100
            + MAX ( Movements[Month] )
            <= SELECTEDVALUE ( 'Calendar'[YEAR] ) * 100
                + SELECTEDVALUE ( 'Calendar'[Month] ),
        CALCULATE (
            SUM ( Movements[balance] ),
            FILTER (
                ALL ( Movements ),
                Movements[MeasureDate] >= _StartDay
                    && Movements[MeasureDate] <= _EndtDay
                    && Movements[Year] = MAX ( Movements[Year] )
                    && Movements[Month] <= MAX ( Movements[Month] )
            )
        ),
        BLANK ()
    )

Result is as below.

1.png

Best Regards,

Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Top Kudoed Authors