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
Hi mohammedismail ,
You could use a slicer with calendar[Date]. Change the slicer type to 'Between'.
Adjust your code so that the 'from' date is MIN(calendar[DATE]), and your 'to' date is MAX(calendar[Date]).
Pete
In fatc, using the date slicer like this, you don't even need the DATESBETWEEN function in your measure. Power BI will aggregate your [Invoice Spend] measure automatically as the end user changes the slicer dates.
Pete
What I forgot to mention is that I'm using another measure to Sum Current year spend.
Okay let me explain what I'm trying to achieve.
I want the users to compare lets say Jan 2021 - May 2021 data with the Jan 2020 - May 2020 ( This selection of months will be dynamic)
So In one Column I need Current year spend and in another column I need Last Year spend. Can you help ? I used SamePeriodLastYear Function using a Dates table but that is throwing an error saying it expects a Contigous selection.
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
