Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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?

 

 

1 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.

1.png

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

View solution in original post

4 REPLIES 4
v-juanli-msft
Community Support
Community Support

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.png2.  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] )
        )
    )
)

3.png

 

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
Not applicable

 

 

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.

Capture.PNG

 

 

 

 

 

 

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")
)))

Capture1111.PNG

 

 

 

 

 

 

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

 

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.

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.

1.png

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.