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
deb_power123
Helper V
Helper V

DAX Query to find Sum of column values per month

Hi All,

 

I have a table say " Website" and have three columns as below "Ranking","Clicks" and "date"  :-

I need to find the sum of the Clicks and Ranking column for present month "August" and previous month "July". It should happen for all upcoming months

 

Ranking Clicks    date
   1 2000 02.08.2021
   2 1200 01.08.2021
   1 2300 01.08.2021
  3 1000 31.07.2021
  4 900 29.07.2021
 -1 200 28.07.2021
 -2 100 27.07.2021
  1 2400 26.07.2021
  2 2100 25.07.2021
  4 1300 24.07.2021
  1 1800 21.07.2021

 

 

Expected values are in yellow : I want DAX query to return the sum of the Ranking and Clicks for Current month and previous month  after extracting month value from date column.

 

 

Expected_Values.JPG

 

 

I tried to use the below DAX for finding sum of click and reputation, but I got this error :-

 

current_month_click = CALCULATE(SUM('Website'[Click]),FILTER('Website',MONTH('Website'[Month])=MONTH(TODAY())))

 

last_month_click= var current_month= MONTH(TODAY()) return  CALCULATE(SUM('Website'[Click]),FILTER('Website',MONTH('Website'[Month])=current_month -1))

 

But it is not working, can someone suggest any modification or any DAX to handle this .

 

 

Kind regards

Sameer

4 REPLIES 4
Jos_Woolley
Solution Sage
Solution Sage

Hi,

Your 'Website' table does not appear to have a column called 'Month'. Perhaps this is the reason for your error.

Try:

 

current_month_click =
CALCULATE (
    SUM ( 'Website'[Clicks] ),
    MONTH ( 'Website'[date] ) = MONTH ( TODAY () )
)

 

Regards

Hi @Jos_Woolley  It didnt return any value for current month after i used the DAX query you suggested above. May be because we just started the month , could you please suggest what changes I can make to the DAX query for getting the result of the previous month. I will check if that is working fine since I have some values for previous month in my data set.Please suggest.

 

P.S: The data I gave above in the same is test data and I tested your suggested code in original data set..

 

 

last_month_click =
CALCULATE (
    SUM ( 'Website'[Clicks] ),
    MONTH ( 'Website'[date] )
        = MONTH ( TODAY () ) - 1
)

Regards

Thankyou for the reponse, I have a last query when I tried to use the same above query you gave to find the month before last month i.e June month then it threw me cirular dependency error .

 

I substracted 2 instead of 1 for last to last month.Have I done something incorrect? Please suggest . Below is the code I changed

last_month_click =
CALCULATE (
    SUM ( 'Website'[Clicks] ),
    MONTH ( 'Website'[date] )
        = MONTH ( TODAY () ) - 2
)

 

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