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_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?

 

 

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

4 Replies

  • 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