DAX Query to find Sum of column values per month

[deb_power123]

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.

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

[Jos_Woolley]

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

[deb_power123]

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

[Jos_Woolley]

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

Regards

[deb_power123]

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
)