Help: Select Dates for a Measure Dynamically

[mohammedismail]

Hi,

I'm stuck in a situation where I need to enter dates Dynamically using any input option that is available.I'm using the below measure - can someone help ?

Spend = CALCULATE(Invoice_Spend[Invoice Spend],DATESBETWEEN(Invoice_Spend[Accounting Date],"04/01/2020","05/31/2020"))

I want to create an Input for the users to select any date that they want to enter.

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

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

[mohammedismail]

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.

[BA_Pete · Accepted solution]

@mohammedismail ,

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

[mohammedismail]

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 ?

[BA_Pete]

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

[mohammedismail]

That's not possible because I also have negative values in the Invoice Spend.

[BA_Pete]

@mohammedismail ,

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

[mohammedismail]

Genuis !! That worked - Thank you! 🙂