CODEWITHSUNDEEP
powerbidax
Saaharjit Singh
Saaharjit Singh

Reputation: 125

Calculating cumulative sum

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)

I was able to calculate the cumulative sum using the below formula

Cumulative_Panels = SUMX ( FILTER ( Query1, WEEKNUM ( Query1[TDATE] ) = WEEKNUM ( EARLIER ( Query1[TDATE]) ) && Query1[TDATE] <= EARLIER ( Query1[TDATE] ) ), Query1[Units] ) This is what i get

enter image description here

Everything is good but the only problem is that the week starts from sunday. As you can see, i want 11/05/2017 to be part of week1 and the new week should start from monday 11/06/2017

Upvotes: 0

Views: 1022

Answers (1)

Foxan Ng
Foxan Ng

Reputation: 7151

WEEKNUM has a parameter where you can set which day the week begins. If you want the week to start from Monday, you can change your WEEKNUM function to:

WEEKNUM(Query1[TDATE], 2) = WEEKNUM(EARLIER(Query1[TDATE]), 2)

where 2 is the parameter I'm talking about.

Details reference here.

WEEKNUM(<date>, <return_type>)

return_type: A number that determines the return value: use 1 when the week begins on Sunday; use 2 when the week begins on Monday. The default is 1.

Upvotes: 0

Related Questions