Forum Discussion
CALCULATE SUM for specific range
Hi experts!
I have a calendar, a dimensional and a transactional table.
Then I have a matrix that is filtered for the current week.
This matrix shows me the sales for the current week.
In addition, I would like to get the sales (table transactional) that happened between the last Saturday and today.
How is this possible by using DAX?
CALCULATE with REMOVEFILTERS?
joshua1990 Well, you could find the last Saturday by doing something like this:
Measure = VAR __Calendar = ADDCOLUMNS(CALENDAR(TODAY()-7,TODAY()),"__Weekday",WEEKDAY([Date],2)) VAR __LastSaturday = MINX(FILTER(__Calendar,[__Weekday]=6),[Date]) RETURN CALCULATE([someting],ALL('Dates'),'Dates'[Date]>=__LastSaturday,'Dates'[Date]<=TODAY())
3 Replies
joshua1990 Correct, you could use REMOVEFILTERS or ALL/ALLEXCEPT in order to override filter context within the measure. Sample data would allow more specificity.
Greg_Deckler : Thanks a lot! How can the range be specified in the function? Any logic that you can share here?
joshua1990 Well, you could find the last Saturday by doing something like this:
Measure = VAR __Calendar = ADDCOLUMNS(CALENDAR(TODAY()-7,TODAY()),"__Weekday",WEEKDAY([Date],2)) VAR __LastSaturday = MINX(FILTER(__Calendar,[__Weekday]=6),[Date]) RETURN CALCULATE([someting],ALL('Dates'),'Dates'[Date]>=__LastSaturday,'Dates'[Date]<=TODAY())
