Want to calculate before data for selected date (may exists or not in master table)

[sarjensystems1]

Hello All,

I want to calculate before value for selected date. for example I have a master table having data from 'Apr-2020' to 'Apr-2021'.
and I have selected 15-oct-2020 , then the measure should calculate Data from Starting date i.e. 'Apr-2020' till '15-oct-2020'.

Thanks in advance.

[Anonymous · Accepted solution]

@sarjensystems1 If you have proper date table you can use DATESBETWEEN as filter for your calculation. For example:

measure=var selectedDate=MAX('date'[date])

return CALCULATE('your_expression', DATESBETWEEN('date'[date],"01.04.2020",selectedDate))

[sarjensystems1 · Accepted solution]

Hi @Anonymous 

The MAX function only accepts a column reference as an argument.
it's giving error like.

[Anonymous · Accepted solution]

@sarjensystems1 If you have proper date table you can use DATESBETWEEN as filter for your calculation. For example:

measure=var selectedDate=MAX('date'[date])

return CALCULATE('your_expression', DATESBETWEEN('date'[date],"01.04.2020",selectedDate))

[sarjensystems1]

hi @Anonymous 

Same I want to calculate after data for selected date, for that Should i Use today function ? replacing '01-04-2021'

[Anonymous]

@sarjensystems1 If you want to calculate for period from today to selected date then yes, you should use TODAY function instead of "01.04.2021". But if you want to calculate for period from selected date to the last date in your dataset then measure should look like this:

measure=var selectedDate=MAX('date'[date])

var maxDate=MAX(ALL('date'[date]))

return CALCULATE('your_expression', DATESBETWEEN('date'[date],selectedDate,maxDate))

[sarjensystems1]

hi @Anonymous 

Thanks for your efforts, one last dax i am looking for is that , can we calculate data from selected date till next 30 days. ("0-30days") and same for "31-60 days".

As you can see, I want to calculate from selected date to next 30 days, 31- next 60 days and so on.

[Anonymous · Accepted solution]

@sarjensystems1 Then you need something like this:

measure=var selectedDate=MAX('date'[date])

return CALCULATE('your_expression', DATESBETWEEN('date'[date],selectedDate+30,selectedDate+60))

That is, you can add to selected date any number of days that's needed.

[sarjensystems1 · Accepted solution]

Hi @Anonymous 

The MAX function only accepts a column reference as an argument.
it's giving error like.

[Anonymous]

@sarjensystems1 That's right, my bad:(

That variable should be like this:

var maxDate=MAXX(ALL('date'[date]), 'date'[date])