Display sum of each week ending for selected month

[DSwezey]

Hi All,

I want to be able to dynamically display the sum of [Sales Amount] for each week of the current selected month.

For example, if I have 10/20/2021 selected then I want all the sums of each week ending for October.

EX: Week ending: 10/24, 10/17, 10/10, 10/3. 

[Syndicate_Admin]

Hi @DSwezey ,

Assuming that your date slicer is using calendar[date], that your calendar is related to your fact table, and that you have a [week ending] field in your calendar table to be used in the visual, you could manage this by removing context filters from within the measure.

Something like this should work:

_monthSales =
VAR __cDate =
MAX(calendar[date])
RETURN
CALCULATE(
    SUM(factTable[Sales Amount]),
    FILTER(
        ALL(calendar),
        MONTH(__cDate) = MONTH(calendar[week ending])
    )
)

This gives me the following ouput:

Pete

[BA_Pete · Accepted solution]

Hi @DSwezey ,

Assuming that your date slicer is using calendar[date], that your calendar is related to your fact table, and that you have a [week ending] field in your calendar table to be used in the visual, you could manage this by removing context filters from within the measure.

Something like this should work:

_monthSales =
VAR __cDate =
MAX(calendar[date])
RETURN
CALCULATE(
    SUM(factTable[Sales Amount]),
    FILTER(
        ALL(calendar),
        MONTH(__cDate) = MONTH(calendar[week ending])
    )
)

This gives me the following ouput:

Pete