Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi All,
I am trying to add a measure that can calculate a Measure (or column) that allows me to calculate the sum of orders from the first of September to the last Sunday (10th of September). The idea that it does it every week the same (dynamic).
As you can see my data is like this:
My current Formula, although looks right on the table (22032), is giving me the right value (14M).
Formula:
Monthly Orders = CALCULATE( SUM('Dailyorders'[orders]); DATESBETWEEN(Datetable[Date]; STARTOFMONTH(Datetable[Date]); (LASTDATE(Datetable[Date])-WEEKDAY(LASTDATE(Datetable[Date]);2))))
Not sure what is missing.
Best,
J
Solved! Go to Solution.
Hi J,
The cause is that the third parameter "end_date" bigger than the second parameter "start_date", which cause the "Datesbetween" return null.
Try this formula please:
Monthly Orders 2 = VAR endOfLastWeek = LASTDATE ( 'Date'[Date] ) - WEEKDAY ( LASTDATE ( 'Date'[Date] ), 2 ) RETURN CALCULATE ( SUM ( 'Sales'[Quantity] ), DATESBETWEEN ( 'Date'[Date], STARTOFMONTH ( 'Date'[Date] ), IF ( STARTOFMONTH ( 'Date'[Date] ) > endOfLastWeek, endOfLastWeek + 7, endOfLastWeek ) ) )
Best Regards!
Dale
Hi J,
The cause is that the third parameter "end_date" bigger than the second parameter "start_date", which cause the "Datesbetween" return null.
Try this formula please:
Monthly Orders 2 = VAR endOfLastWeek = LASTDATE ( 'Date'[Date] ) - WEEKDAY ( LASTDATE ( 'Date'[Date] ), 2 ) RETURN CALCULATE ( SUM ( 'Sales'[Quantity] ), DATESBETWEEN ( 'Date'[Date], STARTOFMONTH ( 'Date'[Date] ), IF ( STARTOFMONTH ( 'Date'[Date] ) > endOfLastWeek, endOfLastWeek + 7, endOfLastWeek ) ) )
Best Regards!
Dale
Hi @v-jiascu-msft,
I just try the calculation. I adapted but still, I get the 14M. I guess the idea will be to limit the measure just for this month. For me, the date period before is not relevant.
I will edit the code as soon as I have a solution.
Best,
J.
Hey,
if you use the measure that works on the table from a card the following happens.
Due to the fact, that there is no active filter from your datetable, this is the difference in contrast to your table visual, the DAX function LASTDATE() will return the last member of your datetable, whereas the function STARTOFMONTH() will return the 1st date of your calendar table.
Regards
Tom
User | Count |
---|---|
97 | |
88 | |
76 | |
70 | |
63 |
User | Count |
---|---|
112 | |
96 | |
95 | |
67 | |
65 |