Calculate value by taking the data from starting till the date selected in slicer

[Anonymous]

Hi all, 

Need help in creating the measure to calculate value from starting date till the date slected in slicer, for example we have data from 2018 jan. If we select relative date as last 60 days, then we need to get data from 2018 jan till today-60 days. So we need subtract those relative date days from today. 

Thanks in advance 

Sai 

[amitchandak · Accepted solution]

@Anonymous , Not very clear , Check this

https://community.powerbi.com/t5/Desktop/Required-custom-date-Slicer-Last-7-days-last-15-days-last-30/m-p/1284966#M561629

[Anonymous]

Hi @Anonymous 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Rico Zhou

[Anonymous · Accepted solution]

Hi @Anonymous 

I think you may want to calculate the values from 2018 Jan(Start Date) untill the calculate date by today - daydiff you select in slicer. Ex if you select last 60 days, you may want to calculate values from 2018 Jan untill (2020/11/05-60). In other words, you need the values in which daydiff >=60.

I build a sample to have a test,

Data Table (I add a dynamicdaydiff column in this table by calculated column): 

DynamicDayDiff = DATEDIFF(Data[Date],TODAY(),DAY)

Slicer Table:

 My Measure:

M.value = 
Var _DayDiff = CALCULATE(MAX(Slicer[DayDiff]),FILTER(Slicer,Slicer[Relative Date]=SELECTEDVALUE(Slicer[Relative Date])))
Var _Value = CALCULATE(SUM(Data[Value]),FILTER(Data,Data[DynamicDayDiff]>=_DayDiff))
Return
_Value

Result is as below.

By default, the table visual will show all values.

Select Last 60 days, it will show results from 2020/01/01 until (2020/11/05-60) = 2020/09/06.

If this reply still couldn't help you solve your problem, please provide me more details about your table, the result you want(screenshots may be better).

Or you can share me with your pbix file by your Onedrive for Business.

You can download the pbix file from this link: Calculate value by taking the data from starting till the date selected in slicer

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[amitchandak · Accepted solution]

@Anonymous , Not very clear , Check this

https://community.powerbi.com/t5/Desktop/Required-custom-date-Slicer-Last-7-days-last-15-days-last-30/m-p/1284966#M561629