Forum Discussion
Help: Select Dates for a Measure Dynamically
Are you using Calendar[Date] in the SAMEPERIODLASTYEAR function?
The contiguous selection error usually shows when you try to implement time intelligence functions (DATESYTD, SAMEPERIODLASTYEAR etc.) using the date field from your fact table (where there isn't always contiguous dates) instead of using the date field from your calendar table (where there ARE contiguous dates by definition).
Your measures should look something like this:
//Measure for current year value _invoiceSpend = SUM(Invoice_Spend[Invoice Spend]) //Measure for prior year value _invoiceSpendPY = CALCULATE( [_invoiceSpend], SAMEPERIODLASTYEAR(Calendar[Date]) )Using these measures with a BETWEEN slicer containing Calendar[Date] should do exactly what you want.
Pete
Thank you so much - this has helped !!
The Slicer also shows Months where I do not have the data - I do not have Data beyond May 2021. Also The dates in the Calendar table are maxed out at May 2021.
I tried applying filter to the visual (Screenshot below) to show dates before May 2021 - even this is not working - Can you help ?
Hi mohammedismail ,
Difficult to say without seeing your model etc., but I would filter the date slicer visual using a numerical measure. For example, you could put your [_invoiceSpend] measure into the visual-level filter then set it to [_invoiceSpend] > 0 to only show dates/months where there is actual invoice spend.
Pete
That's not possible because I also have negative values in the Invoice Spend.
Ok, just adjust the visual level filter to something like: [_invoiceSpend] is not blank.
If that doesn't work, then you could create a basic measure to identify if there's any data, something like:
_filterMeasure = COUNTROWS(Invoice_Spend)Then use this measure in the visual-level filter as [_filterMeasure] > 0.
Pete
Genuis !! That worked - Thank you! 🙂