Forum Discussion

edson_souza's avatar
edson_souza
Regular Visitor
7 years ago
Solved

DAX Formula Accumulate only the current month

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

Need Help
  • edson_souza's avatar
    edson_souza
    7 years ago

    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.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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

     

     

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    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

  • edson_souza's avatar
    edson_souza
    Regular Visitor
    7 years ago

    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.