DAX Formula Accumulate only the current month

[edson_souza]

I have historical values and need a measure to accumulate values only for the current month dynamically via DAX. How do I do?

I have:

Date Value
19/09/2018 300,00
20/09/2018 781,00
21/09/2018 56,00
22/09/2018 144,00
23/09/2018 155,00
24/09/2018 1.478,00
25/09/2018 1.500,00
26/09/2018 1.500,00
27/09/2018 2.000,00
28/09/2018 600,00
29/09/2018 300,00
30/09/2018 781,00
01/10/2018 56,00
02/10/2018 144,00
03/10/2018 155,00
09/10/2018 1.478,00
10/10/2018 1.500,00
11/10/2018 1.500,00
12/10/2018 2.000,00
13/10/2018 600,00

I need:

Date Value
01/10/2018 56,00
02/10/2018 200,00
03/10/2018 355,00
04/10/2018 355,00
05/10/2018 355,00
06/10/2018 355,00
07/10/2018 355,00
08/10/2018 355,00
09/10/2018 1.833,00
10/10/2018 3.333,00
11/10/2018 4.833,00
12/10/2018 6.833,00
13/10/2018 7.433,00

[edson_souza · Accepted solution]

Hi, @Anonymous and @v-yulgu-msft. 

I solved by mingling your help.

1) I created the calendar;
2) I created a measure to load only values of the current month:

Value Current Month = 
CALCULATE(
    SUM('Fact'[Value]); 
    FILTER('Calendar';MONTH('Calendar'[Dates]) = MONTH(TODAY())); 
    FILTER('Calendar';YEAR('Calendar'[Dates]) = YEAR(TODAY()))
    )

3) And then I used DATESMTD in the created measure:

Value Current month MTD = 
CALCULATE(
    [Value Current Month];
    DATESMTD(Calendar[Dates])
   )

 https://imgur.com/a/oV2j2Wj

Thank you very much.

[v-yulgu-msft]

Hi @edson_souza,

Create a calendar table, and set a one to many relationship between calendar table and data table based on [Date] field.

dim date = CALENDAR(MIN(Sheet4[Date]),MAX(Sheet4[Date])) 

Add 'dim date'[Date] and below measure into a table visual.

accumulate values =
CALCULATE (
    SUM ( Sheet4[Value] ),
    FILTER (
        ALL ( 'dim date' ),
        'dim date'[Date].[MonthNo] = MONTH ( TODAY () )
            && 'dim date'[Date] <= MAX ( 'dim date'[Date] )
    )
)

Best regards,

Yuliana Gu

[Anonymous]

From your sample result i see that you need MTD value.

Current mnth value= CALCULATE(SUM(Sales_Fact.Sales),DATESMTD(Dates[Date]))

If you look for current full month, then u can try this

Current mnth value= CALCULATE(SUM(Sales_Fact.Sales), Month(Table[date1]= Month(TODAY()) & Year(Table[date1]= Year(TODAY()) 

Thanks
Raj