Custom Measure based on Slicer

[Anonymous]

Hi,

I have a requirement where i need to create two different measures based on StartDate & EndDate in the Data Slicer.

For E.g. If the selected Slicer is StartDate 10/1/2020  EndDate 10/31/2020

Then BegingingSales = SUM(Sales) WHERE Date < 10/1/2020

         OutStandingSales = SUM(Sales) Where Date < 10/31/2020

Thanks for your help

[v-shex-msft]

HI @Anonymous,

Did amitchandak 's formulas help your scenario? If this is a case, you can consider accepting it as the solution to help others who have the same requirement to find it quickly. If not, please share more detailed decisions and feel free to post there.

Regards,

Xiaoxin Sheng

[amitchandak · Accepted solution]

@Anonymous , Try measure like, Assume you have date table

BegingingSales =
var _min =minx(allselected(date),date[date])
return
calculate(sum(Table[sales]), filter(all(Date), Date[Date] <_min))

OutStandingSales =
var _max =maxx(allselected(date),date[date])
return
calculate(sum(Table[sales]), filter(all(Date), Date[Date] <=_max))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[Anonymous]

Thanks @amitchandak  i like the idea will try and keep you posted