Error in DAX command filtering for last 6 months

[_chris_]

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

[MFelix · Accepted solution]

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

[_chris_]

I think I understand that my approach was more for a calculated column.

I guess this is a better way to make use of a measure:

Anzahl Last 6 Months = CALCULATE(SUM(SupportStatusTickets[Anzahl]); FILTER(SupportStatusTickets; SupportStatusTickets[Anlage]>DATE(YEAR(TODAY()); MONTH(TODAY())-6; DAY(TODAY()))))

[MFelix]

Hi @_chris_,

This is a completly different approach from what you were presenting in the first post, now you are returning the sum of the values based on the comparision of the dates. Not sure what is the expected result you want but this measure gives a different outcome than the first one.

Regards,

MFelix