Forum Discussion
Helper IIIError in DAX command filtering for last 6 months
Hi,
I have a Datasource with Support Tickets. I want to create a line diagram showing the support tickets per week but only created in the last 6 months. So I was thinking of creating a measure giving back TRUE/FALSE and then adding this the the visual filter
Using this expression and adding the measure to the filter, I cannot see anything in the filter:
Letzte 6 Monate = IF(CALCULATE(SUM(SupportStatusTickets[CreationDate]))>DATE(YEAR(TODAY()); MONTH(TODAY())-6; DAY(TODAY())); TRUE; FALSE)
Trying to make a string out of it I get an error message while filtering for "Yes"
Letzte 6 Monate = IF(CALCULATE(SUM(SupportStatusTickets[CreationDate]))>DATE(YEAR(TODAY()); MONTH(TODAY())-6; DAY(TODAY())); "Yes"; "No")
Any help would be great.
Thx Chris
Hi _chris_,
The problem is that you are comparing the sum of a date (returns an integer) to a date six month prior.
Your measure should be something like this
Letzte 6 Monate =
IF (
MAX ( SupportStatusTickets[CreationDate] )
> DATE ( YEAR ( TODAY () ); MONTH ( TODAY () ) - 6; DAY ( TODAY () ) );
"Yes";
"No"
)This will get the date you are looking at and compare it to 6 months before today.
Regards,
MFelix
5 Replies
- MFelix
Super User
Hi _chris_,
The problem is that you are comparing the sum of a date (returns an integer) to a date six month prior.
Your measure should be something like this
Letzte 6 Monate =
IF (
MAX ( SupportStatusTickets[CreationDate] )
> DATE ( YEAR ( TODAY () ); MONTH ( TODAY () ) - 6; DAY ( TODAY () ) );
"Yes";
"No"
)This will get the date you are looking at and compare it to 6 months before today.
Regards,
MFelix
_chris_Hi MFelix, thanks a lot. This works.
However, I do not understand why :-) Initially I thought, I just should do IF(SupportTicket[CreationDate]....To me this would have been the logical thing. However, I got an error message which made me use the Calculate function. I guess, I need to read a bit more about it. Still do not understand why to use MAX as I thought it would be calculated for every row.
2nd thing I do not understand: If I want to return TRUE/FALSE I cannot choose anything in the filter panel. My expectation was to get 2 checkboxes instead of nothing. I feel a boolean datatype would express better the result instead of a string (and probably with large datasets should be quicker)
BR Christian
- MFelix
Hi _chris_,
I'm assuming you are using a measure, not a calculated column. When using a measure DAX uses context to get you the final result, this means that depending on the values you have on your visuals, filters or slicers the calculations will be different.
In you case you have the additional fact that you were comparing different types of data, because when you made the calculated formula you transform the date to a integer since you were making a sum. To keep the same data type you need to select an aggregator that is inline with the data type you are using in this case, and since this is used in a measure the data need to be aggregated by MAX or MIN.
Breaking down the measure you are getting the MAXIMUM value for date and then comparing it to the date 6 months ago and getting a yes or no.
The second part of your question depends on your setup and how you want to make the interactions between the data but if you only add the measure below you should get a bollean TRUE/FALSE.
Letzte 6 Monate =
MAX ( SupportStatusTickets[CreationDate] )
> DATE ( YEAR ( TODAY () ); MONTH ( TODAY () ) - 6; DAY ( TODAY () ) )Regards,
MFelix