Cumulative Total using a Formatted Time Display Column

[S_loke]

Hi,

Coming to an issue where cumulative totals are difficult to calculate where a custom display of Month is required.

Source data has:

Date

Category

Count

The data is displayed as a line and stacked column graph with the shared axis being time.  However, as the format is preferred to be MMM-YY a custom column was inserted.

The cumulative total works fine as the following DAX

CALCULATE (
    'CPC'[Count],
    FILTER (all(CPC),'CPC'[PERIOD].[Date] <= MAX ( 'CPC'[PERIOD].[Date] )
    )

But using the custom column for format, it evaluates incorrectly as MMM-YY is now treated as text

CALCULATE (
    'CPC'[Count],
    FILTER (all(CPC),'CPC'[Display Time] <= MAX ( 'CPC'[Display Time] )
    )

Is there any syntax where I can force the "<=" evaluation to be time accurate based on the text field?

[v-sihou-msft]

@S_loke

No, you can't do comparison like "<=" on text fields.

However, in this scenario, you've already created a custom column based on Date, which means each formatted time is associated with the date. It will not impact your cumulative total calculation. Just keep the original formula:

CALCULATE (
    'CPC'[Count],
    FILTER (all(CPC),'CPC'[PERIOD].[Date] <= MAX ( 'CPC'[PERIOD].[Date] )
    )

And put the 'CPC'[Display Time] into your chart visual, it supposes to return correct result.

Regards,

[S_loke]

Thanks.

I've tried to complete the graphics with the custom period display.  However, the calculation does come up correctly.

however, when using the real date field, it does show it correctly.

is this due to the calculation being a measure instead of a calculated column?  I guess that maybe the measure has some dynamic relationship on how it's calculated?