Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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
)
User | Count |
---|---|
41 | |
27 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
19 | |
18 | |
15 |