How can I stop cumulative total?

[Anonymous]

Hi, how can I stop the cumulative measure to stop at June? and reflect only blank values starting July?

1

Some DAX I tried:

#1. 

Cumulative try (II) = VAR _max = MAXX(ALL('Date Table- DIM'[Date]),'Date Table- DIM'[Date])

Return

CALCULATE('Important Measures'[Actuals(I)],FILTER(ALL('Date Table- DIM'[Date]),'Date Table- DIM'[Date]<=MAX('Date Table- DIM'[Date]) && 'Date Table- DIM'[Date]<=_max))

#2. 

Cumulative Actuals try (I) = VAR LastDatee = CALCULATE(MAX('Date Table- DIM'[Date]),LASTDATE('Date Table- DIM'[Date]))

VAR VisualDate = MAX('Date Table- DIM'[Date])

Return

IF(VisualDate<=LastDatee, CALCULATE('Important Measures'[Actuals(I)],FILTER(ALLSELECTED('Date Table- DIM'[Date]),'Date Table- DIM'[Date]<=MAX('Date Table- DIM'[Date]))))

Both are not solving the purpose (Stopping cumulative from July Onwards)

Thank you so much in advance 🙂

[SpartaBI]

@Anonymous best practice is to add a column in your date table that checks for the last available date with sales, something like
DateWithSales =
INT('Date'[Date] <= MAX ( Sales[Order Date] ))
Then add it as filter to the filter pane of the report and set it to 1.

You can also do it robust to not need to add it to the filter pane by adding it to all your measures.
Somthing like, create this measure:

ShowValueForDates :=
VAR LastDateWithData =
    CALCULATE (
        MAX ( 'Sales'[Order Date] ),
        REMOVEFILTERS ()
    )
VAR FirstDateVisible =
    MIN ( 'Date'[Date] )
VAR Result =
    FirstDateVisible <= LastDateWithData
RETURN
    Result

And then add it to your measure:

Sales YTD :=
IF (
    [ShowValueForDates],
    CALCULATE (
        [Sales Amount],
        DATESYTD ( 'Date'[Date] )
    )
)

Anyway, for full explanation just go to the source 🙂 sqlbi.com:
https://www.daxpatterns.com/standard-time-related-calculations/

Showcase Report – Contoso By SpartaBI

      

[ribisht17 · Accepted solution]

@Anonymous 

Please refer to this solution Solved: Cumulative total - stopping after last date not wo... - Microsoft Power BI Community

Regards,

Ritesh

[Anonymous]

Thank you @ribisht17  I had to make some tweaks to make it work.  Actually, the column I am using to generate the cumulative series is a measure. I defined the measure again in the maxx() function. This worked.