Cumulative total with date table

[Jim_PBI]

Hello, 

I'm creating a cumulative line chart with total sales for 2 years of data (2021, 2022). I've created a date table as some dates are missing from my sales table, and it was causing an issue when the slicer was applied. The line for 2022 is currently continuing for dates in the future, is there a way to stop the 2022 line at today's date and allow the 2021 line to continue? 

I'm using the 'Year' field on the legend to create two lines. 

My measure is below: 

Cumulative sales =

CALCULATE(

    Sales[Sum_Sales],

    FILTER(

        ALL('Date'[Date]),

        'Date'[Date] <= MAX ('Date'[Date])

    )

)

[Anonymous]

Hi @Jim_PBI ,

You could try to add a visula-level filter/measure to limit the dates.

Add a filter to a report in Power BI - Power BI | Microsoft Docs

Best Regards,

Stephen Tao

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

[Daryl-Lynch-Bzy]

@Jim_PBI - you could consider adding a Filter to the Visual so date is less than and equal to Today, or if you need to show the remaining month just with no data instead of straight-line, update your function with something like:

Cumulative sales =
VAR _Today = Today()
RETURN
CALCULATE(
    Sales[Sum_Sales],
    FILTER(
        ALL('Date'[Date]),
        'Date'[Date] <= MAX ('Date'[Date]),
        'Date'[Date] <= _Today
    )
)

OR 

Cumulative sales =
VAR _Today = Today()
RETURN
IF ( MAX( 'Date'[Date] ) <= _Today , 
CALCULATE(
    Sales[Sum_Sales],
    FILTER(
        ALL('Date'[Date]),
        'Date'[Date] <= MAX ('Date'[Date]),
    )
) , BLANK() )

[Daryl-Lynch-Bzy · Accepted solution]

@Jim_PBI - you could consider adding a Filter to the Visual so date is less than and equal to Today, or if you need to show the remaining month just with no data instead of straight-line, update your function with something like:

Cumulative sales =
VAR _Today = Today()
RETURN
CALCULATE(
    Sales[Sum_Sales],
    FILTER(
        ALL('Date'[Date]),
        'Date'[Date] <= MAX ('Date'[Date]),
        'Date'[Date] <= _Today
    )
)

OR 

Cumulative sales =
VAR _Today = Today()
RETURN
IF ( MAX( 'Date'[Date] ) <= _Today , 
CALCULATE(
    Sales[Sum_Sales],
    FILTER(
        ALL('Date'[Date]),
        'Date'[Date] <= MAX ('Date'[Date]),
    )
) , BLANK() )