March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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
Solved! Go to 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]) )
Thank you very much.
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]) )
Thank you very much.
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
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
115 | |
76 | |
57 | |
52 | |
44 |
User | Count |
---|---|
164 | |
116 | |
63 | |
57 | |
50 |