Cumulative total until end of month regardless of date range

[RMBiH]

Hello everyone,

I have a line chart that displays the cumulative total sales based on a selected date range.

This date is used for drillthrough to other pages (I've removed the drillthrough buttons for simplicity here). Therefore, I want the line chart to always display the cumulative value up to the last day of the month for the selected end date, regardless of the slicer's selected end date. Currently I'm using

 ALLSELECTED()

in my measure to respect the slicer context, and as expected, it calculates the cumulative sales until the selected end date. However, the challenge is that I want the line chart to always extend the cumulative total sales until the end of the month for the selected end date, not just up to the date selected in the slicer.

I have achieved the same result using 

ALL()

in the following DAX measure:

Goal = 

VAR firstDay = CALCULATE(MIN(Dim_Date[Date]), ALLSELECTED(Dim_Date))

RETURN
CALCULATE(
    SUM(Goals[Goal]),
    FILTER(
        ALL(Dim_Date),
        Dim_Date[Date] >= firstDay && Dim_Date[Date] <= MAX(Dim_Date[Date])
    )
)

But again, this will still display cumulative values only up to the selected end date from the slicer. The issue is that if I use

ENDOFMONTH(Dim_Date[Date])

instead of

MAX(Dim_Date[Date])

I'll always get the same total because the cumulative sum would be calculated for each data point up to the last day of the month. This isn’t what I want, the cumulative total should continue calculating for each individual date up to that date, even beyond the selected end date, until the last day of the month.

Has anyone encountered a similar issue or have suggestions on how to modify the measure to achive this behaviour?

Any help would be much appreciated!

[lbendlin]

I want the line chart to always display the cumulative value up to the last day of the month for the selected end date, regardless of the slicer's selected end date

This means your slicer must be fed by a disconnected table.

[RMBiH]

Hi, I may not have explained the situation clearly enough. The date slicer is synced across all pages, and this page serves as the home page for drillthrough to other pages. For this reason, I really need the slicer to remain consistent with the one on the other pages. I just can't figure out a way to modify the context in the measure for that specific purpose, or maybe there's a way to "trick" the line chart into treating the end date (e.g., 23.12.2024) as the 31.12.2024. In that case, I would get the desired result, if that's possible.

[pcoley]

@RMBiH 
Please try this solution:
Generate a copy of your dateColumn and rename it as EOMDate[Date].
Use it as the x-axis field at the line chart.

EOMDate = ALL( 'Date'[Date])

Use the next measure to obtain de cummulative until the end of the month of the date selected at the line chart.

YourGoal =
VAR _early =
    CALCULATE( MIN( 'Dim_Date'[Date] ), ALLSELECTED( 'Dim_Date'[Date] ) )
VAR _latter =
    EOMONTH(
        CALCULATE( MAX( 'Dim_Date'[Date] ), ALLSELECTED( 'Dim_Date'[Date] ) ),
        0
    )
RETURN
    CALCULATE(
        SUMX(
            FILTER(
                ALL( 'Dim_Date'[Date] ),
                'Dim_Date'[Date] >= _early
                    && 'Dim_Date'[Date] <= MAX( EOMDate[Date] )
            ),
            SUM( Goals[Goal] )//YOUR MEASURE
        ),
        TREATAS( EOMDate, 'Dim_Date'[Date] ),
        KEEPFILTERS( EOMDate[Date] <= _latter )
    )

 Hope this help, if so please accept the solution. Kudos are welcome😀

[RMBiH]

@pcoley thank you this is very helpful! However, when the selected date range spans multiple months, the measure behaves unexpectedly on the first day of a new month. Specifically, it adds a larger cumulative total from the previous month than the real value to the first day of the new month.

To debug I created a matrix with rows for EOMDate[Date] and Dim_Date[Date], using YourGoal as the values to see what is being added for each date. I got results that I honestly don't understand, but I thought it might be helpful for you to review.

I would appreciate any further adjustments you might suggest, thanks again.

[pcoley · Accepted solution]

@RMBiH 
With the same logic using the EOMDate table as the x-axis field,

EOMDate = ALL( 'Date'[Date])

Please try with this 2 measures: 

GoalCurrent = 
VAR _current =  MAX( EOMDate[Date] )
VAR _Selected=  ALLSELECTED(Dim_Date[Date])
VAR _early =    MINX(_Selected, 'Dim_Date'[Date] )
VAR _latter =   EOMONTH( MAXX(_Selected, 'Dim_Date'[Date] ), 0)
VAR _check =    DATESBETWEEN( EOMDate[Date], _early, _latter )
VAR _test =     DATESBETWEEN( EOMDate[Date], _early, _current)
RETURN
    IF(
        NOT MAX( EOMDate[Date] ) IN _Check,
        BLANK(),
        CALCULATE(
            CALCULATE(
                SUMX( Goals, Goals[Goal] ),
                TREATAS( EOMDate, Dim_Date[Date] )
            ),
            EOMDate[Date] IN _test 
        )
    )

GoalLastYear = 
VAR _current =  MAX( EOMDate[Date] )
VAR _Selected=  ALLSELECTED(Dim_Date[Date])
VAR _early =    MINX(_Selected, 'Dim_Date'[Date] )
VAR _latter =   EOMONTH( MAXX(_Selected, 'Dim_Date'[Date] ), 0)
VAR _check =    DATESBETWEEN( EOMDate[Date], _early, _latter )
VAR _test =     SAMEPERIODLASTYEAR(DATESBETWEEN( EOMDate[Date], _early, _current))
RETURN
    IF(
        NOT MAX( EOMDate[Date] ) IN _Check,
        BLANK(),
        CALCULATE(
            CALCULATE(
                SUMX( Goals, Goals[Goal] ),
                TREATAS( EOMDate, Dim_Date[Date] )
            ),
            EOMDate[Date] IN _test 
        )
    )

I hope this helps, if so please mark it as a solution. Kudos are appreciated😀

.

[RMBiH]

@pcoley Unfortunately, these didn't work either. As the model is very complicated, I have created a simplified .pbix with the necessary tables only, which I am attaching a link to, as for some reason it doesn't seem possible to attach .pbix files here.

Thank you once again, I really appreciate your help!

[pcoley]

@RMBiH 
Please check that you have not selected the "Auto date/time" at the Time Intelligence of the DataLoad configuration options.

The measures to use are GoalCurrent and GoalLastYear. 
In the actual data set there are only values for the 2024, therefore the measure for the previuos year won´t show any value.

I hope this helps, if so please mark it as a solution. Kudos are appreciated😁

[RMBiH]

@pcoley Great, instead of disabling "Auto date/time" I made the EOMDate Date on X-axis a date column instead of a hierarchy

And finally, it does work as expected!!!

Thank you so much. To be honest, I don't totally understand how it works and I would appreciate it if you could give a brief explanation of what this measure is doing exactly as well as why it doesn't work with date hierarchy.

But anyway, I will mark your answer as a solution.