Drawing the graph for cumulative sum data

[Anonymous]

Below is dax for cumulative sales sum which is grouped by 'SBU name' and 'month'

Cumulative Sales_Blank = if('2-3-1_BIS_WorkingDay_mKRW_Each BU'[Date]<=today(),CALCULATE(SUM('2-3-1_BIS_WorkingDay_mKRW_Each BU'[Sales_mKRW]),

FILTER('2-3-1_BIS_WorkingDay_mKRW_Each BU','2-3-1_BIS_WorkingDay_mKRW_Each BU'[sbu_name]=EARLIER('2-3-1_BIS_WorkingDay_mKRW_Each BU'[sbu_name])

&& '2-3-1_BIS_WorkingDay_mKRW_Each BU'[Date] <= EARLIER('2-3-1_BIS_WorkingDay_mKRW_Each BU'[Date])

&& FORMAT('2-3-1_BIS_WorkingDay_mKRW_Each BU'[Date],"YYYYMM") = FORMAT(EARLIER('2-3-1_BIS_WorkingDay_mKRW_Each BU'[Date]),"YYYYMM")

)))

The problem is that if I draw a line graph with above number, it shows until today even though sales data is actually not existed until today.

So I tried to change dax as below,

Cumulative Sales_Blank = if('2-3-1_BIS_WorkingDay_mKRW_Each BU'[Date]<=CALCULATE(LASTDATE('2-3-1_BIS_WorkingDay_mKRW_Each BU'[Date]),ISBLANK('2-3-1_BIS_WorkingDay_mKRW_Each BU'[Sales_mKRW])=FALSE()),CALCULATE(SUM('2-3-1_BIS_WorkingDay_mKRW_Each BU'[Sales_mKRW]),
 FILTER('2-3-1_BIS_WorkingDay_mKRW_Each BU','2-3-1_BIS_WorkingDay_mKRW_Each BU'[sbu_name]=EARLIER('2-3-1_BIS_WorkingDay_mKRW_Each BU'[sbu_name])
 && '2-3-1_BIS_WorkingDay_mKRW_Each BU'[Date] <= EARLIER('2-3-1_BIS_WorkingDay_mKRW_Each BU'[Date])
 && FORMAT('2-3-1_BIS_WorkingDay_mKRW_Each BU'[Date],"YYYYMM") = FORMAT(EARLIER('2-3-1_BIS_WorkingDay_mKRW_Each BU'[Date]),"YYYYMM")
)))

And then it shows until the data is existed but the line graph shows disconnected when the date there is no sales data.

Would you please revise my dax to draw correct line graph?

[v-juanli-msft]

Hi @Anonymous 

I can reproduce your problem,

1. shows until today even though sales data is actually not existed until today

2019/3/28 has blank value for "sales_mkrw" field.

2.  it shows until the data is existed, but the line graph shows disconnected when the date there is no sales data

2019/3/13 has blank value for "sales_mkrw" field.

Cumulative Sales_Blank =
IF (
    [date]
        <= CALCULATE (
            LASTDATE ( Sheet5[date] ),
            FILTER ( Sheet5, NOT ( ISBLANK ( Sheet5[sales_mkrw] ) ) )
        ),
    CALCULATE (
        SUM ( [sales_mkrw] ),
        FILTER (
            Sheet5,
            [sbu_name] = EARLIER ( Sheet5[sbu_name] )
                && [date] <= EARLIER ( [date] )
                && [year/month] = EARLIER ( Sheet5[year/month] )
        )
    )
)

Is my understanding correct?

What final result do you want?

Best Regards

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

As you can see in below picture, even though sales data is only existed until 21th, graph shows the data until 27th which is wrong with below dax.

Cumulative Sales_Blank = if('2-3-1_BIS_WorkingDay_mKRW_Each BU'[Date]<=today(),CALCULATE(SUM('2-3-1_BIS_WorkingDay_mKRW_Each BU'[Sales_mKRW]),
 FILTER('2-3-1_BIS_WorkingDay_mKRW_Each BU','2-3-1_BIS_WorkingDay_mKRW_Each BU'[sbu_name]=EARLIER('2-3-1_BIS_WorkingDay_mKRW_Each BU'[sbu_name])
 && '2-3-1_BIS_WorkingDay_mKRW_Each BU'[Date] <= EARLIER('2-3-1_BIS_WorkingDay_mKRW_Each BU'[Date])
 && FORMAT('2-3-1_BIS_WorkingDay_mKRW_Each BU'[Date],"YYYYMM") = FORMAT(EARLIER('2-3-1_BIS_WorkingDay_mKRW_Each BU'[Date]),"YYYYMM")
)))

So I change above as below but the line graph (please only refer to the black line) is disconnected when in the date there is no sales data.

Cumulative Sales_Blank = if('2-3-1_BIS_WorkingDay_mKRW_Each BU'[Date]<=CALCULATE(LASTDATE('2-3-1_BIS_WorkingDay_mKRW_Each BU'[Date]),ISBLANK('2-3-1_BIS_WorkingDay_mKRW_Each BU'[Sales_mKRW])=FALSE()),CALCULATE(SUM('2-3-1_BIS_WorkingDay_mKRW_Each BU'[Sales_mKRW]),
 FILTER('2-3-1_BIS_WorkingDay_mKRW_Each BU','2-3-1_BIS_WorkingDay_mKRW_Each BU'[sbu_name]=EARLIER('2-3-1_BIS_WorkingDay_mKRW_Each BU'[sbu_name])
 && '2-3-1_BIS_WorkingDay_mKRW_Each BU'[Date] <= EARLIER('2-3-1_BIS_WorkingDay_mKRW_Each BU'[Date])
 && FORMAT('2-3-1_BIS_WorkingDay_mKRW_Each BU'[Date],"YYYYMM") = FORMAT(EARLIER('2-3-1_BIS_WorkingDay_mKRW_Each BU'[Date]),"YYYYMM")
)))

Would you please how to connect that line? Please give a comment by revising dax

[v-juanli-msft]

Hi 

Is this problem sloved?

If not, please let me know.

Best Regards

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

When i create a calcuated column like this (not measure)

Cumulative Sales_Blank =
IF (
    [date]
        <= CALCULATE (
            LASTDATE ( Sheet5[date] ),
            FILTER ( Sheet5, ISBLANK ( Sheet5[sales_mkrw] ) = FALSE () )
        ),
    CALCULATE (
        SUM ( [sales_mkrw] ),
        FILTER (
            Sheet5,
            [sbu_name] = EARLIER ( Sheet5[sbu_name] )
                && [date] <= EARLIER ( [date] )
                && FORMAT ( Sheet5[date], "yyyymm" )
                    = FORMAT ( EARLIER ( Sheet5[date] ), "yyyymm" )
        )
    )
)

It works correctly on my side.

I change the X type to categorical, add "date" from the a date table which is connected to my Sheet5.

You could download my pbix to see what i have done and see difference with yours.

Best Regards

Maggie