Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Drawing the graph for cumulative sum data

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_WorkingDa...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
7 years ago

 

 

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's avatar
v-juanli-msft
Icon for Community Support rankCommunity Support
7 years ago

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