Forum Discussion

ringovski's avatar
ringovski
Helper II
4 years ago
Solved

Sum for current month

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

  • 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

1 Reply

  • 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