Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ringovski
Helper II
Helper II

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

ringovski_0-1661483412694.jpeg

lookup label: Metric Name

ringovski_1-1661483475469.jpeg

Date Table:Calendar 

ringovski_2-1661483511262.jpeg

 

Line Chart as table with expected value 399.81

ringovski_3-1661483575565.jpeg

 

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

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

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

View solution in original post

1 REPLY 1
amitchandak
Super User
Super User

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors