Forum Discussion

Jim_PBI's avatar
Jim_PBI
Frequent Visitor
4 years ago
Solved

Cumulative total with date table

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])
    )
)
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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() )

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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() )

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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() )