Forum Discussion

ringovski's avatar
ringovski
Icon for Helper II rankHelper 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: Met...
  • amitchandak's avatar
    amitchandak
    4 years ago

    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

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

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