Forum Discussion

smmoore34's avatar
smmoore34
Icon for Advocate II rankAdvocate II
1 year ago
Solved

when creating a measure using a date table the date column is not recognized

I have a date table marked as a date table with a column called date. I want to create a measure to compare YTD record totals to LYTD record totals. The first measure I want to create is: 

YTD_total = (
DISTINCTCOUNT ( 'CIVIL_AssignedOnly'[callid] ),
  'DATE'[Date]
)
However, the error I receive is "cannot find name date".
I've confirmed the column exists, and is linked to the date field in the other table with the same data type. I've also confirmed that auto date time is turned off in my file.
I appreciate any assistance!
 
 
Need Help
  • MasonMA's avatar
    MasonMA
    1 year ago

    Hello smmoore34

     

    If your date table is already marked as Date Table (If not, in Modeling 'Mark as Date Table'), then use below time intelligence function, 

     

    YTD=
    TOTALYTD(
    DISTINCTCOUNT('CIVIL_AssignedOnly'[callid]),
    'DATE'[Date]
    )

     

    LYTD =

    CALCULATE(
        DISTINCTCOUNT('CIVIL_AssignedOnly'[callid]),
        SAMEPERIODLASTYEAR('DATE'[Date])
    )

     

7 Replies

  • MasonMA's avatar
    MasonMA
    Icon for Super User rankSuper User
    1 year ago

    Hello smmoore34

     

    If your date table is already marked as Date Table (If not, in Modeling 'Mark as Date Table'), then use below time intelligence function, 

     

    YTD=
    TOTALYTD(
    DISTINCTCOUNT('CIVIL_AssignedOnly'[callid]),
    'DATE'[Date]
    )

     

    LYTD =

    CALCULATE(
        DISTINCTCOUNT('CIVIL_AssignedOnly'[callid]),
        SAMEPERIODLASTYEAR('DATE'[Date])
    )

     

    • smmoore34's avatar
      smmoore34
      Icon for Advocate II rankAdvocate II
      1 year ago

      Thank-you!! I had left out the 

      "TOTALYTD"
      I appreciate the help.
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    1 year ago

    Hi,

    Try this measure

    YTD_total = calculate(DISTINCTCOUNT ( 'CIVIL_AssignedOnly'[callid] ),datesytd('DATE'[Date]))

    Hope this helps.

  • Shahid12523's avatar
    Shahid12523
    Icon for Community Champion rankCommunity Champion
    1 year ago

    You can’t just put 'DATE'[Date] in a measure. Use time-intelligence functions:

     

    YTD:

    YTD_total =
    CALCULATE(
    DISTINCTCOUNT('CIVIL_AssignedOnly'[callid]),
    DATESYTD('DATE'[Date])
    )


    LYTD:

    LYTD_total =
    CALCULATE(
    DISTINCTCOUNT('CIVIL_AssignedOnly'[callid]),
    DATESYTD(SAMEPERIODLASTYEAR('DATE'[Date]))
    )


    ✅ Make sure the Date table is marked as a date table and related properly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi smmoore34 ,

    Thank you for reaching out to the Microsoft fabric community forum. 

     

    Could you please confirm if the issue has been resolved. I wanted to check if you had the opportunity to review the information provided by MasonMA , Ashish_Mathur  and Shahid12523  helpful for you to resolve your issue. Please feel free to contact us if you have any further questions.

     

    Thank you.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi smmoore34 ,

     

    Could you please confirm if the issue has been resolved.  Please feel free to contact us if you have any further questions.

     

    Thank you.