Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Dears,
I'm struggling creating a measure containing sum of visits for current week/month.
I've managed to do this for previous and current years by using the following formula:
Visits 2016 =
VAR
CurrentDate = DATE(2016,12,31)
RETURN
CALCULATE(SUM(Main[Visits]),DateKey[Year] = YEAR(CurrentDate))
Any ideas on how to do the same thing but for the current week and month?
I'm trying to create one measure which will always display the total visits for the current week/month for a dashboard.
I've tried replacing Year with Month (bolded below) but no succes.
VAR
CurrentDate = DATE(2016,12,31)
RETURN
CALCULATE(SUM(Main[Visits]),DateKey[Month] = Month(CurrentDate))
I get the following error:
Error Message:
MdxScript(Model) (32, 32) Calculation error in measure 'Main'[Visits 2016]: DAX comparison operations do not support comparing values of type Text with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values.
I'm also mentioning that i have a DateKey table for dates and another table with Visits. A relationship is created between them with Date (one to one 1:1)
It would be of great help if you could point me in the right direction.
Thanks in advance,
Andrei
Solved! Go to Solution.
Hi @avelio,
Based on the error message, the DateKey[Month] is text.
If the format of DateKey[Month] likes Jan, Feb etc, you should change your formula to:
VAR CurrentDate = DATE(2016,12,31) RETURN CALCULATE(SUM(Main[Visits]),Filter(DateKey,DateKey[Month] = FORMAT(CurrentDate,"MMM")))
If the format of DateKey[Month] likes January, February etc, use FORMAT(CurrentDate,"MMMM").
Do the same thing for the current week, you also need to confirm the data type and format of DateKey[week] is same with week of CurrentDate.
Format(Date,"DDD") returns Thu, while Format(Date,"DDDD") returns Thursday.
Please feel free to ask if you meet other problems.
Best Regards,
Angelia
Thanks for your reply @Phil_Seamark.
Can you also assisst me in changing the format so that it would work?
Also i don't really know if the formula i used is the best approach to this. If you have some tips for me it would be much appreciated.
I'm quite new to DAX and PowerBI and still trying to get the grasp of it.
Thanks so much,
Andrei
Hi @avelio,
Based on the error message, the DateKey[Month] is text.
If the format of DateKey[Month] likes Jan, Feb etc, you should change your formula to:
VAR CurrentDate = DATE(2016,12,31) RETURN CALCULATE(SUM(Main[Visits]),Filter(DateKey,DateKey[Month] = FORMAT(CurrentDate,"MMM")))
If the format of DateKey[Month] likes January, February etc, use FORMAT(CurrentDate,"MMMM").
Do the same thing for the current week, you also need to confirm the data type and format of DateKey[week] is same with week of CurrentDate.
Format(Date,"DDD") returns Thu, while Format(Date,"DDDD") returns Thursday.
Please feel free to ask if you meet other problems.
Best Regards,
Angelia
@v-huizhn-msft Thanks!
It worked!
you've been of great help.
Turns out i just had to switch the field between Month(jan,feb) to Month Number and it was ok.
BR,
Andrei
Hi @avelio
You may want to allow for years. Otherwise you'll find your January 2016 and January 2017 data will be combined into a single January bucket,
Dear @Phil_Seamark thanks for your input!
That's the problem that i've encountered but managed to fix it by adding the bolded text:
This Month Visits =
VAR
CurrentDate = TODAY()
RETURN
CALCULATE(SUM(Main[Visits]),(DateKey[Month number] = MONTH(CurrentDate)), DateKey[Year] = YEAR(CurrentDate))
Respectively,
This Week Visits =
VAR
CurrentDate = TODAY()
RETURN
CALCULATE(SUM(Main[Visits]),(DateKey[Week] = WEEKNUM(CurrentDate)), DateKey[Year] = YEAR(CurrentDate))
Does it look okay in your opinion?
BR,
Andrei
Looks pretty good. Well done 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |