cancel
Showing results for
Did you mean:
Post Prodigy

## 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?

1 ACCEPTED SOLUTION
Super User

@joshua1990 Well, you could find the last Saturday by doing something like this:

``````Measure =
VAR __LastSaturday = MINX(FILTER(__Calendar,[__Weekday]=6),[Date])
RETURN
CALCULATE([someting],ALL('Dates'),'Dates'[Date]>=__LastSaturday,'Dates'[Date]<=TODAY())``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Learn Power BI 2nd Edition
3 REPLIES 3
Super User

@joshua1990 Correct, you could use REMOVEFILTERS or ALL/ALLEXCEPT in order to override filter context within the measure. Sample data would allow more specificity.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Learn Power BI 2nd Edition
Post Prodigy

@Greg_Deckler : Thanks a lot! How can the range be specified in the function? Any logic that you can share here?

Super User

@joshua1990 Well, you could find the last Saturday by doing something like this:

``````Measure =
VAR __LastSaturday = MINX(FILTER(__Calendar,[__Weekday]=6),[Date])
RETURN
CALCULATE([someting],ALL('Dates'),'Dates'[Date]>=__LastSaturday,'Dates'[Date]<=TODAY())``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Learn Power BI 2nd Edition

Announcements

#### Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

#### The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

#### Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

#### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Top Solution Authors
Top Kudoed Authors