Last date in slicer regardless of visual by month

[SOppeneer]

Hi All,

I hope you can help me, as it seems I can't figure this one out. 

I have a report with a calendar table ('Report Calendar') that I use to filter a fact table ('Invoice Detail') on separate date columns, which is working out well so far. However, I need to obtain the last date of the selection for another calculation (here: 12/31/2018). Also that works fine, until I want to trend the measure by the month. As you can see below, the measure takes the last date of each month in the visual which is kind of expected, while I want it to apply the selection's last value of 12/31/2018 for each month. How do I achieve this?

The slicer takes the 'Report Calendar'[Date]. The table shows the [Date] from 'Report Calendar', and the [c. Period End (Posted)] and [c. Count Invoices (Posted)] measures from 'Invoice Detail'.

[SOppeneer · Accepted solution]

As usual, in retrospect the solution is pretty easy. Instead of trying to use the same date for both slicer and trend visual, I added another date calendar with the same range (Trend Calendar as SUMMARIZE for the Report Calendar) so that I can trend with the Trend Calendar while using the Report Calendar as slicer and for the last date of selected period. 

The count measure considers both the Report Calendar and the Trend Calendar. 

c. Count Invoices (Posted) = 
VAR dateStart = MIN('Report Calendar'[Date]) RETURN
VAR dateEnd = MAX('Report Calendar'[Date]) RETURN
VAR trendStart = MIN('Trend Calendar'[Date]) RETURN
VAR trendEnd = MAX('Trend Calendar'[Date]) RETURN

CALCULATE(
    COUNT('Invoice Detail'[Financial Doc # (Yr-CC-Doc)]), 
    FILTER(
        'Invoice Detail', 
        'Invoice Detail'[Date Posting] >= dateStart && 
        'Invoice Detail'[Date Posting] <= dateEnd &&
        'Invoice Detail'[Date Posting] >= trendStart &&
        'Invoice Detail'[Date Posting] <= trendEnd
    )
)

 

[v-easonf-msft]

Hi,  @SOppeneer 

Thanks for your sharing.

More people will learn new things here.

Best Regards,
Community Support Team _ Eason