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

     

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