Sum for current month

ringovski — Fri, 26 Aug 2022 03:21:27 GMT

Hi All,

I need to sum a numeric value for the current month, each month. Data model has a realtionship between Metric[Month SK] and Calendar[Date SK].

data table: Metric

lookup label: Metric Name

Date Table:Calendar

Line Chart as table with expected value 399.81

Measure in line chart:

CALCULATE (SUM('Metric'[Numeric Value])

,'Metric Name'[MetricName] = "Total General Waste recycled"

CALCULATE (SUM('Metric'[Numeric Value])

,'Metric Name'[MetricName] = "Total General Waste sent to landfill"

)

Current Month Measure returns blank:

# Total Waste Current Month =

VAR CurrentMonth = MONTH(TODAY())

VAR TotalWasteMTD = CALCULATE (

SUM('Metric'[Numeric Value]),'Metric Name'[MetricName] = "Total General Waste recycled",

'Calendar'[Month] = CurrentMonth

)

CALCULATE (SUM('Metric'[Numeric Value]),'Metric Name'[MetricName] = "Total General Waste sent to landfill",

'Calendar'[Month] = CurrentMonth

)

RETURN

TotalwasteMTD

This should be simple but I'm not sure what's wrong.

Thanks

Re: Sum for current month

amitchandak — Fri, 26 Aug 2022 03:41:27 GMT

ringovski , Try like

calculate( SUM('Metric'[Numeric Value]),filter('Metric Name' ,'Metric Name'[MetricName] = "Total General Waste recycled"),Filter('Calendar' ,eomonth('Calendar'[Date],0) = eomonth(Today(),0)))
+calculate( SUM('Metric'[Numeric Value]),filter('Metric Name' ,'Metric Name'[MetricName] = "Total General Waste sent to landfill"),Filter('Calendar' ,eomonth('Calendar'[Date],0) = eomonth(Today(),0)))

If some date is selected and you want to ignore

calculate( SUM('Metric'[Numeric Value]),filter('Metric Name' ,'Metric Name'[MetricName] = "Total General Waste recycled"),Filter(all('Calendar' ),eomonth('Calendar'[Date],0) = eomonth(Today(),0)))
+calculate( SUM('Metric'[Numeric Value]),filter('Metric Name' ,'Metric Name'[MetricName] = "Total General Waste sent to landfill"),Filter(all('Calendar' ),eomonth('Calendar'[Date],0) = eomonth(Today(),0)))

refer if needed: https://medium.com/chandakamit/cheat-sheet-power-bi-time-intelligence-formulas-using-today-654f26e27304

topic Sum for current month in DAX Commands and Tips

Sum for current month

Re: Sum for current month