Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

A function 'PLACEHOLDER' has been used in a True/False expression that is used as a table filter exp

Hello;   When trying to run a calculation I am getting the error described in the title. Here is the dax expression:   currentMonthPunchedHours = CALCULATE(sum(punches[hours]), and(True, and(YE...
Need Help
Fowmy's avatar
Fowmy
Super User
4 years ago

Anonymous 

I added a Dates table and calculated the four measures as requested, please check the attached PBIX file below my signature.

---------------------------------
-- Calculated Table: '__Measures'
---------------------------------
TABLE '__Measures' = {BLANK()}

-------------------------
-- Measure: [Punch Hours]
-------------------------
MEASURE '__Measures'[Punch Hours] = 
    VAR __CurrentDate = MAX('Dates'[Date])
    VAR __MonthStart = EOMONTH(MAX('Dates'[Date]),-1)+1
    RETURN
    CALCULATE(
        SUM(punches[hours]),
        punches[punchIn] >= __MonthStart,
        punches[punchOut] <= __CurrentDate
    )
   

----------------------------
-- Measure: [Punch Hours PM]
----------------------------
MEASURE '__Measures'[Punch Hours PM] = 
    VAR __CurrentDate = EDATE(MAX('Dates'[Date]),-1)
    VAR __MonthStart = EOMONTH(MAX('Dates'[Date]),-2)+1
    RETURN
    CALCULATE(
        SUM(punches[hours]),
        punches[punchIn] >= __MonthStart,
        punches[punchOut] <= __CurrentDate
    )
   

----------------------------
-- Measure: [Schedule Hours]
----------------------------
MEASURE '__Measures'[Schedule Hours] = 
    VAR __CurrentDate = MAX('Dates'[Date])
    VAR __MonthEnd = EOMONTH(MAX('Dates'[Date]),0)
    RETURN
    CALCULATE(
        SUM(scheduled[hours]),
        scheduled[begDate] > __CurrentDate,
        scheduled[endDate] <= __MonthEnd
    )

-------------------------------
-- Measure: [Schedule Hours PM]
-------------------------------
MEASURE '__Measures'[Schedule Hours PM] = 
    VAR __CurrentDate = EDATE(MAX('Dates'[Date]),-1)
    VAR __MonthEnd = EOMONTH(MAX('Dates'[Date]),-1)
    RETURN
    CALCULATE(
        SUM(scheduled[hours]),
        scheduled[begDate] > __CurrentDate,
        scheduled[endDate] <= __MonthEnd
    )
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thank you for the help,

     

    Would it be possible to have the same thing but with a slider only on the day number rather than selecting a time interval ? (tried it but can't manage to get results)

     

    Thank you very much.

  • Fowmy's avatar
    Fowmy
    Super User
    4 years ago

    Anonymous 

    You to choose a particular date, for this click on the small downward arrow in the slicer and select "Before", this will allow you to choose a single end date


    If you need to be able to choose day like 1 or 15 or 20 for examle, then you need have Year , Month and Day added in the slicer

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    I would like to be able to have something similar to this:

    And using that filter, we would compute the number of punch hours (sum until the day number of the slider) and the scheduled hours (from the slider point to the end of the month). Tried to do it on my side but I can't manage to get it.

     

    Thank you for your support and help

Related content