Forum Discussion
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
Do you have any suggestions?
Thank you !
10 Replies
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.Hi,
thanks for your quick response.
Your measure works when i visualize this:
But my wish is to make a chart where the following result without using column Date:
unfortuantly you see that the amount of 45 million comes on every row....
any suggestions ? thanks
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?
