cumulative sum of values for a week

[Saaharjit]

I have the below table

I want to display bar graph that has the cumulative sum of Panels for a week. Then after the week it refreshes starts again for the next week. We are using Date completed column to figure out the week.

For eg.

week 1:

1st nov - 10 panels are produced 

2nd nov - 10 panels are produced (the bar graph would have 20 panels for 2nd nov)

3rd nov - 10 panels are produced (the bar graph would have 30 panels for 3rd nov)

...

Example for the graph

[Zubair_Muhammad · Accepted solution]

Hi @Saaharjit

Try adding this Calculated Column

Cumulative_Panels =
SUMX (
    FILTER (
        TableName,
        WEEKNUM ( TableName[Date_Completed] )
            = WEEKNUM ( EARLIER ( TableName[Date_Completed] ) )
            && TableName[Date_Completed] <= EARLIER ( TableName[Date_Completed] )
    ),
    TableName[Panels]
)

[Zubair_Muhammad · Accepted solution]

Hi @Saaharjit

Try adding this Calculated Column

Cumulative_Panels =
SUMX (
    FILTER (
        TableName,
        WEEKNUM ( TableName[Date_Completed] )
            = WEEKNUM ( EARLIER ( TableName[Date_Completed] ) )
            && TableName[Date_Completed] <= EARLIER ( TableName[Date_Completed] )
    ),
    TableName[Panels]
)

[Saaharjit]

@Zubair_Muhammad Why does the new column start the week from sundaY? i want the beginning of the week to be monday is that possible?

[Zubair_Muhammad]

Hi
I will check it and let you know

[Zubair_Muhammad · Accepted solution]

HI @Saaharjit

Use WEEKDAY(TableName[DateColumn],2).

It will start your week from Monday

[Saaharjit]

@Zubair_Muhammad Thanks for all your help. I have another question and was wondering if you could help.

Below is my data table. Is it possible to get a cumulative sum of all units in a SEQ_NO.

For Example - on 01/03/2018 number of units produced in SEQ_NO 101 is 48. The next date is 6 and then 18 and so on. I want the graph to add to show 48 on  01/03/2018 then the next day add 6 and show 54 units (48 + 6 = 54) and then add 18 and so on. Then for the Next SEQ_NO(102) start again.

[Zubair_Muhammad]

HI @Saaharjit

As a calculated column, try

Column =
CALCULATE (
    SUM ( TableName[UNITS] ),
    FILTER (
        ALLEXCEPT ( TableName, TableName[SEQ_NO] ),
        TableName[TDATE] <= EARLIER ( TableName[TDATE] )
    )
)

[Zubair_Muhammad]

@Saaharjit

As a MEASURE, try

Measure =
CALCULATE (
    SUM ( TableName[UNITS] ),
    FILTER (
        ALLEXCEPT ( TableName, TableName[SEQ_NO] ),
        TableName[TDATE] <= SELECTEDVALUE ( TableName[TDATE] )
    )
)

[Saaharjit]

@Zubair_Muhammad Can you help me with the problem? 

http://community.powerbi.com/t5/Desktop/How-to-compare-two-columns/m-p/368909#M167094 

[Zubair_Muhammad]

@Saaharjit

[Saaharjit]

Hey, thanks alot for your reply. I am getting the correct value for the cumulative panels but when i put that onto the bar graph for  it does the sum(cumulative panels)  so the values i get on the bar graph are wrong. Because i have number of enteries for on a single day, how can i only display t the cumulative sum values of that date onto the bar graph

[Zubair_Muhammad]

There is an option of don't summarize
Average might work too

[Saaharjit]

Thanks i was able to fix it but how can i only display the current week on the bar graph and the week starts from sunday, can it not start from monday