Forum Discussion
Create measure for current month
Hi Folks,
Could you please help with current month measure.
I need to show sum of data for current month. In my table month displayed like this:
How can I write new measure, that display sales by current month?
YerlaSDU Okay then you can create a new column with below codE:-
Column 2 = VAR _len = LEN ( 'Table (5)'[Column] ) RETURN MID ( 'Table (5)'[Column], 4, _len - 3 )Output:-
And now create a measure with this code:-
Measure = CALCULATE ( SUM ( 'Table (5)'[Column1] ), FILTER ( 'Table (5)', 'Table (5)'[Column 2] = FORMAT ( TODAY (), "MMMM" ) ) )
4 Replies
YerlaSDU , I am assuming you have date column
This month Today =
var _min = eomonth(today(),-1)+1
var _max = eomonth(today(),0) //today()
return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Table'),'Table'[Date] >= _min && 'Table'[Date] <=_max ) )Or You can use Time Intelligence
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKAHi YerlaSDU ,
If it is a date type column then you can use this measure -
Measure = CALCULATE ( SUM ( 'Table'[Column] ), FORMAT ( 'Table'[date], "MMMM" ) = FORMAT ( TODAY (), "MMMM" ) )or if it is text type column then you can use this:-
Measure = CALCULATE ( SUM ( 'Table'[Column1] ), FILTER ( 'Table', CONTAINSSTRING ( 'Table'[date], FORMAT ( TODAY (), "MMMM" ) ) ) )Thanks,
Samarth
Not working for me. I think my column has text format and need to create new column for month and work with it. Could you please help with this?
YerlaSDU Okay then you can create a new column with below codE:-
Column 2 = VAR _len = LEN ( 'Table (5)'[Column] ) RETURN MID ( 'Table (5)'[Column], 4, _len - 3 )Output:-
And now create a measure with this code:-
Measure = CALCULATE ( SUM ( 'Table (5)'[Column1] ), FILTER ( 'Table (5)', 'Table (5)'[Column 2] = FORMAT ( TODAY (), "MMMM" ) ) )
