Measure returning sum of visits for current week and month

[avelio]

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

[Phil_Seamark]

Hi there

Your datekey[month] column is text but the MONTH function returns a number.

[avelio]

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

[v-huizhn-msft · Accepted 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

[avelio]

@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

[Phil_Seamark]

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,

[avelio]

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

[Phil_Seamark]

Looks pretty good.  Well done 🙂