Forum Discussion

erikm's avatar
erikm
Frequent Visitor
5 years ago
Solved

Issue with displaying rolling totals

Hi, I am trying to accomplish the following scenario: User selects one date (that is the end date of a selection) The beginning of the date range is automatically set to January 1st of the previo...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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.

    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. 

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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.

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's avatar
erikm
Frequent Visitor
5 years ago

Hi Anonymous 

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 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.

    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.