Is it possible to use calculated field in DATEADD function?

[Anonymous]

Hi everyone!

I have a date slicer and according to the number of days selected I need to calculate another time range.

For example, in a slicer I select a range from 03-08-2021 to 03-14-2021 that is 7 days and in this case my formula should looks like DATEADD(calendar[date], -7, DAY). If i select another one time range, for example  from 02-22-2021 to 03-14-2021 that is 21 days , my formula should have changed and looks like DATEADD(calendar[date], -21, DAY).

Is it possible to use a calculated field in DATEADD function?

THank you!

[VijayP]

@Anonymous 

I have checked and it is working. VAR is taking the Slicer Selection very correctly

[Anonymous]

Please let me know if you understand how it can be possible.

[Anonymous]

  

[Anonymous]

Thank you for answe.

I write a formula according to your advice: 

Column = Calculate(countrows('Date Table'),filter(all('Date Table'),'Date Table'[Date]>=MIN('Date Table'[Date]) &&

'Date Table'[Date]<=MAX('Date Table'[Date]))) - 1

But when I select different periods in a slicer, I get the same number: 31 that is the namber of days in my Date table.

[VijayP]

@Anonymous 

I think I am not able to understand the final outcome. I think you were asking question for getting variable of number Intervals in DateAdd function based on selection,

Please let me know one more time what is the final outcome you want! 

Is that by each Date number of Days?! or any other outcome which can be added as a picture

[Anonymous]

So, finally I want to illustrate two charts over two different periods according to the slicer selection: Current and Previous Period.

But I can't use formulas like SAMEPERIODLASTYEAR because in a slicer different range can be selected. And I need to illustrate for example current week and previous week OR current month and previous month and so on.

[v-janeyg-msft · Accepted solution]

Hi, @Anonymous 

According to your description, I think in fact, powerbi has ready-made functions that can be used, and it is easy to understand. If you write dax, you need to create by situation, and it can't be done in one step.

You can create two date slicers, then use 'relative date' feature, then use 'edit interactions' functions to control the displayed results according to the corresponding slicer.

Like this:

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

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

[VijayP]

@Anonymous 

DATEADD Function Attribute is just showin number intervals based on the interval unit (DAY,MONTH etc) selected, it is not exactly number of days. 
to achieve number of intervals, 

VAR numberofdays = Calculate(countrows(dates),filter(all(Dates),datedim[date]>=min(Datedim[date]) && 
datedim[date]<=max(Datedim[date]))*-1
Return
CAlculate(yourmeasure, numberofdays,day))

LEt me know if it works!

[Anonymous]

Hi! Thanks a lot for your answer! But the part with VAR doesn't take into account slicer selections. It caluates all dates in my date table. How can it be corrected?