cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
regasanyoto Member
Member

Issue Cumulative Sales based on Week

Hi guys,

 

i am worried about the issue that i had couple days ago and today is my deadline to deliver a report.

 

Well lets start by explaining the used measures:

 

Total Revenues = SUM('Sales invoice line'[Line amount MST])
 
 
Total Revenues LY = CALCULATE([Total Revenues]; 'Dim Calender Sales Invoice'[Year] = (YEAR(TODAY())-1))

 

Cumulative Revenues LY = CALCULATE( [Total Revenues LY];
                                                FILTER(ALLSELECTED('Sales invoice line');
                                                     'Sales invoice line'[Invoice date] <= MAX('Sales invoice line'[Invoice date])))
 
 
 
The  data model is as following:
 
SalesInvoiceLine.Invoice Date (many to one) Dim Calender Sales Invoice.Date
 
 
The issue is following:
 
in week of year 1 until 3 you see the same number of 45.250.128. i expect different output which is 
 
week 1:  639.422
week 2:  1.420. 457
week 3:  2.149. 058
 
 
 
 screenshot sales invoice line.png
 
Do you have any suggestions?
 
Thank you !
 
10 REPLIES 10
tex628 New Contributor
New Contributor

Re: Issue Cumulative Sales based on Week

Cumulative Revenues LY = 
Var MaxDate = MAX('Dim Calender Sales Invoice'[Date])
Var LastYear = YEAR(TODAY())-1

Return
Calculate([Total Revenues LY];
ALL('Dim Calender Sales Invoice');
'Dim Calender Sales Invoice'[Year] = LastYear;
'Dim Calender Sales Invoice'[Date] <= MaxDate 
)


If you have the correct relationship between yout invoice table and your calendar this should work i believe.

regasanyoto Member
Member

Re: Issue Cumulative Sales based on Week

Hi,

 

@tex628

 

thanks for your quick response.

 

Your measure works when i visualize this:

 

screenshot sales invoice line part 1.png

 

But my wish is to make a chart where the following result without using column Date

 

screenshot sales invoice line part 2.png 

 

unfortuantly you see that the amount of 45 million comes on every row....

 

 

any suggestions ? thanks

tex628 New Contributor
New Contributor

Re: Issue Cumulative Sales based on Week

Create a week column in you datetable: 

Week = WEEKNUM(Calendar[Date];2)

Use it instead of the "Week of Year" column that you are currently using or do you have to use a week column that is outside of the calendar table?

regasanyoto Member
Member

Re: Issue Cumulative Sales based on Week

Hey it still doestn work...

it still give me the same amount of 45 mil on every row based on your suggested calculated colum in the same date table.

 

I  dont bother to use a a week column inside or outside  the table

tex628 New Contributor
New Contributor

Re: Issue Cumulative Sales based on Week

Dim Calendar Sales Invoice [Date] and Dim Calendar Sales Invoice [Week] are both in the same table and it still doesn't work?

Shouldnt be possible...

Can u pull both date and week into the matrix and take a picture?
regasanyoto Member
Member

Re: Issue Cumulative Sales based on Week

screenshot 2.png

tex628 New Contributor
New Contributor

Re: Issue Cumulative Sales based on Week

And with the amount measures in the matrix aswell!

regasanyoto Member
Member

Re: Issue Cumulative Sales based on Week

screenshot 5.jpg

tex628 New Contributor
New Contributor

Re: Issue Cumulative Sales based on Week

Cumulative Revenues LY = 
Var MaxDate = MAX('Dim Calender Sales Invoice'[Date])
Var LastYear = YEAR(TODAY())-1

Return
Calculate([Total Revenues];
ALL('Dim Calender Sales Invoice');
'Dim Calender Sales Invoice'[Year] = LastYear;
'Dim Calender Sales Invoice'[Date] <= MaxDate 
)

 
Replace [Total Revenues LY] with [Total Revenues] in the measure above. 

I'm acutally not sure what is causing this behaviour, it's really strange. For some reason the 

'Dim Calender Sales Invoice'[Year] = LastYear;

 Filter isn't doing what it should. You should not have 2019 and 2020 appear in the matrix. 


Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 132 members 1,679 guests
Please welcome our newest community members: