DAX for Cumulative Sum For Current Month (Ignoring Date Slicer), including Activity Slicer

[Asantos2020]

Hello guys,

Here is the situation:

Sales Table:

Date            Activity          Total
07/07/19    Retail              $1,000

07/08/2019 Retail             $1,500

Calendar Table

Date

Activity as Slicer

Relationship duly established.

I need to have a DAX that would give me the cumulative total for current month based, ignoring a Date Slicer and considering the Activity in Slicer.

So far, I got and this and I can't figure out how to include Activity here... 

Total Cumulativo = CALCULATE(SUM(Sales Table[Total]);ALL(Sales Table[Date]))

 Thanks a million!

[v-juanli-msft]

Hi @Asantos2020 

Based on my understanding, you want to get Cumulative Sum For Current Month through all years.

If so, you could edit interactions for the slicer and other visuals as below

Then create measures

current month = MONTH(TODAY())

month of the date = MONTH(MAX(Sheet1[date]))

Measure =
CALCULATE (
    SUM ( Sheet1[value] ),
    FILTER (
        ALLSELECTED ( Sheet1 ),
        [current month] = [month of the date]
            && Sheet1[date] <= MAX ( Sheet1[date] )
    )
)

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Asantos2020]

Hello @v-juanli-msft ,

I actually needed it to show current year totals up to current month. I've managed to do it, but throughtout current year. I'll try your suggestion later on. For now, I'll leave it as it is...with months to come showing on the chart.

Thank you!

[Cmcmahan]

I'm assuming by current month, you mean the current actual month, as opposed to the contextual current month.

Total Cumulativo = CALCULATE(SUM(Sales Table[Total]);ALLEXCEPT('Sales Table'; 'Sales Table'[Activity]))

This will sum all totals in your table that are currently selected by your slicer.   

[Asantos2020]

Hello @Cmcmahan !

Thanks for your response. However, this is not aggregating by date, though:

I have a Date Slicer, filtering the other visuals to Today. This visual, though, should display how we are doing through current month. 

Curiously, it doesn't even show 2019 now, so I could try drilling down.

Any hint as to why this is happening?

Thanks!

Regards,

Antonio

[Cmcmahan]

Depends on the value of that line.  If that line is zero, then the measure isn't working or isn't getting any results.  If that line has a number (like 2500) then it's working for the entire current year, and you need to drill down to the month instead of the year.

If you hit the forked down arrow, and get down to month, I'd be more interested in the results there.

[Asantos2020]

Hi @Cmcmahan !

After playing around with it, I've gotten to the following, but it isn't cumulative, though and I can't seem to make it work with this calculation: 

Fat. Cumulativo = CALCULATE(SUM(BI_Vendas[Faturamento]);FILTER('Calendário';MONTH('Calendário'[Date])=MONTH(TODAY())))

[Cmcmahan]

So what is this a sum of?  All values from the current month?

You need to add a term somewhere in your filter that includes all dates less than the current date being evaluated.

Something like 

Fat. Cumulativo = CALCULATE(SUM(BI_Vendas[Faturamento]);FILTER('Calendário';MONTH('Calendário'[Date])=MONTH(TODAY()) && 'Calendário'[Date] < SELECTEDVALUE('Calendário'[Date]) ))

[Asantos2020]

Hi @Cmcmahan !

It actually doesn't even allow me to look at it from the month standpoint, as it takes all dates from the calendar table I created (2014 through 2030), giving me the total sum for this time range (...so not zero, but a flat total):

See that it doesn't allow me to drill down in any way, though.

Any other tweak I'm missing?

Thanks!

Antonio