Forum Discussion

PBI_Curious's avatar
PBI_Curious
Frequent Visitor
5 years ago
Solved

Using date parameters within a measure to filter by slicer selection

I've got a calendar table with date columns for month#, month_year etc but also additional column where month_year is formatted as text because I want a short month format can't see a date format for mmm yyyy in PowerBI.

 

I've got dynamic month labels on my report that work off a selected date that works like this:

Selected Date = var selecteddate = SELECTEDVALUE('Calendar'[Month Year_TXT]) return If(ISBLANK(selecteddate), "All Months", selecteddate)

 

I've got a source data table with date fields for "Opened" (when a service ticket was opened) and "ReportingMonth".  I need a measure that will count the items in the "Opened" column where the date falls within the Selected Date period (current report month) and also where "ReportingMonth" falls within Selected Date period.

 

I've tried this:

 

Tickets Received = CALCULATE(COUNT(Track_Data[Opened]), Track_Data[ReportingMonth]= [Selected Date], Track_Data[Opened] = [Selected Date])

 

Error message CALCULATE function can't be used with True/False expression.  How else could I go about this?

Need Help

2 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    5 years ago

    PBI_Curious try this:

     

    Tickets Received = 
VAR __selectedDate = [Selected Date]
RETURN
CALCULATE(
COUNT(Track_Data[Opened]), 
Track_Data[ReportingMonth]= __selectedDate , 
Track_Data[Opened] = __selectedDate 
)

     

    Check my latest blog post Improve UX: Show Year in Legend When Using Time Intelligence Measures | PeryTUS IT Solutions  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

  • v-easonf-msft's avatar
    v-easonf-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi, PBI_Curious 

    As mentioned by  parry2k , when creating a new measure, it is recommended to use a variable to encapsulate the referenced measure.

     

    Could you please tell me whether the problem has been solved?
    If so,  please mark the helpful replies or add your reply as Answered to close this thread.
    It will help other community members easily find the solution when they get the similar issue.

     

    Best Regards,
    Community Support Team _ Eason