Sum for current month

[ringovski]

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

[amitchandak · Accepted solution]

@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-654f26e27...