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
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
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