Forum Discussion

spencercw's avatar
spencercw
New Member
3 years ago

Measure Filtered by Date > Today()

For some reason DAX is much less intuitive than SQL to me. 

 

I am trying to count up all future appointments.

 

Visits                                 

VisitKeyVisitDateKeyVisitTypeKey
120221030101
220221018102
320221121103

 

VisitType

VisitTypeKeyVisitTypeName
101New Client
102Maintenance

 

Date

DateKeyDateValue (Date)WeekYear
2022101110/15/2022 12:00:00 AM202241
2022101210/16/2022 12:00:00 AM202241

 

Relationships:

Visits.VisitTypeKey = VisitType.VisitTypeKey

Date.DateKey = Visits.VisitDateKey

 

I am trying to only count up Visits that happen after today. My current measure is:

 

Future Appointments =
VAR start_date = FILTER(ALL(DateDim[datevalue]), datedim[datevalue]>= today())
RETURN CALCULATE(COUNT(VisitFact[VisitKey]),start_date)
 
Future Appointments will be the Values. VisitType the Legend and WeekYear the Axis. 
 
It works when I put VisitDateKey in the axis, but when I try to use the week field that is in my date table, the measure breaks and every week has the same value.
 
Thank you!
 
Edit: I suspect I have done something wrong with my modeling, but I don't understand what. I suspect this because:
 
I am able to 'solve' this problem by making a calulated binary column in VisitFact called IsInFuture based on the VisitDateKey. This does show appointments decreasing in future weeks (which we expect). 
Need Help

4 Replies

  • johnt75's avatar
    johnt75
    Super User
    3 years ago

    Try

    Future appointments =
CALCULATE ( COUNTROWS ( Visits ), 'Date'[DateValue] >= TODAY () )
    • spencercw's avatar
      spencercw
      New Member
      3 years ago

      This did not work. Similar to my solution, when I put it into a line chart, every future week shows the exact same values. 

       

      My Date table is marked as an official date table. Does that affect this?

      • johnt75's avatar
        johnt75
        Super User
        3 years ago

        The code I posted is overwriting the filter from the chart. Try

        Future appointments =
CALCULATE ( COUNTROWS ( Visits ), KEEPFILTERS('Date'[DateValue] >= TODAY () ) )
  • v-xiaotang's avatar
    v-xiaotang
    Community Support
    3 years ago

    Hi spencercw 

    Thanks for reaching out to us.

    I just want to confirm if you resolved this issue? If yes, you can accept the answer helpful as the solution or share you method and accept it as solution, thanks for your contribution to improve Power BI.

    If you need more help, please let me know.

     

    Best Regards,

    Community Support Team _Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.