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

v-juanli-msft's avatar
v-juanli-msft
Community Support
7 years ago

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'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
      Community 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

    • v-juanli-msft's avatar
      v-juanli-msft
      Community Support
      7 years ago

      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.