Forum Discussion

Fernando_PBI's avatar
Fernando_PBI
Regular Visitor
3 years ago

How to stop cumulative values in graph/table after no more data?

Hi,

 

I used the New quick measure assitant to get a cumulative view of data, I was able to achieve somehwhat the result I was looking for, however, the line chart/table shows the total in the following months where there is no data.

 

For example, 1246 in Nov 2019 should be 0 by Dec 2019 given that there's no more information for the latter.

How can I stop this from happening?

 

 

The generated DAX code from the New Quick Measure assitant is the following:

 

Qty running total in Month =
CALCULATE(
    SUM('Table'[Qty]),
    FILTER(
        CALCULATETABLE(
            SUMMARIZE(
                'Sort Order Table',
                'Sort Order Table'[Month Number],
                'Sort Order Table'[Month]
            ),
            ALLSELECTED('Sort Order Table')
        ),
        ISONORAFTER(
            'Sort Order Table'[Month Number], MAX('Sort Order Table'[Month Number]), DESC,
            'Sort Order Table'[Month], MAX('Sort Order Table'[Month]), DESC
        )
    )
)
 

Thank you in advance!

Need Help

1 Reply

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    Fernando_PBI ,

    With help from date table

     

    If(not(isblank(SUM('Table'[Qty]))) , CALCULATE(SUM('Table'[Qty]),filter(allselected(date),date[date] <=max(date[Date]))) , blank())