Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Functionality of date table columns with DAX

I've made myself a date table in DAX with the following formula:

 

Date =
ADDCOLUMNS(
CALENDAR(MIN('transaction'[created_date]),MAX('transaction'[created_date])),
"Year", YEAR([Date]),
"Month", FORMAT([Date], "mmmm"),
"Month Number", MONTH([Date]),
"Quarter", FORMAT([Date], "\QQ"),
"Month Year", FORMAT([Date], "mmm yyyy")
)

 

The most important column I needed to add was "Month Year" to show how many months each user was marked true for a binary indicator. I made this new table using SUMMARIZECOLUMNS() and joining the relevant columns from other tables and was able to get the count, but I am concerned that it is not accurate because the "Month Year" column is text not date. The binary indicator is based on a date equation so how can it interact with a text column as a date? 

 

Secondly, I have to make a measure showing how many months it has been since that indicator was True for each user. I use:

 

DATEDIFF( 

CALCULATE( MAX( [Month Year] ), FILTER( 'Table', [status] == "True" ), 

TODAY(),

MONTH)

 

The max Month Year is acting as text and so does the max alphabetically. 

 

Just to sum my question: Will that text column for month year in my date column cause my date/time based calculations to be inaccurate? 

 

Thanks.

General Comment
Need Help
  • v-jingzhang's avatar
    v-jingzhang
    3 years ago

    Hi Anonymous 

     

    You can add the following two columns to your date table. The first "Year Month Number" column is of Number type, which can be used to compare YearMonth values correctly. The second "Month Start" column returns the first date of every month, which is of Date type. 

    "Year Month Number", YEAR([Date])*100+MONTH([Date]),
"Month Start", DATE(YEAR([Date]),MONTH([Date]),1)

     

    With above columns, you can use a measure like 

    measure =
DATEDIFF (
    CALCULATE ( MAX ( 'Date'[Month Start] ), 'Table'[status] = "True" ),
    TODAY (),
    MONTH
)

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it. Highly appreciate your Kudos!

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    Anonymous , You should take max date, and if do not have date create one using month year

     

    DATEDIFF( 

    CALCULATE( MAX( [Date] ), FILTER( 'Table', [status] == "True" ), 

    TODAY(),

    MONTH)

     

    or

     

    DATEDIFF( 

    CALCULATE( eomonth(MAX( [Date] ),0) , FILTER( 'Table', [status] == "True" ), 

    TODAY(),

    MONTH)

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Unfortunately, that resulted in a value of 0 for all users. 

  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi Anonymous 

     

    You can add the following two columns to your date table. The first "Year Month Number" column is of Number type, which can be used to compare YearMonth values correctly. The second "Month Start" column returns the first date of every month, which is of Date type. 

    "Year Month Number", YEAR([Date])*100+MONTH([Date]),
"Month Start", DATE(YEAR([Date]),MONTH([Date]),1)

     

    With above columns, you can use a measure like 

    measure =
DATEDIFF (
    CALCULATE ( MAX ( 'Date'[Month Start] ), 'Table'[status] = "True" ),
    TODAY (),
    MONTH
)

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it. Highly appreciate your Kudos!