DATE RANGE DYNAMIC

[SQUILES]

HI...I need help!!

I need to clasified my date column by period (current and prev). Each period is from day 6th to day 5th(next month).

I need just to show the last two period.

Example:

from: 2/5/2020 to: 3/6/2020 = prev_period

from: 3/6/2020 to: 4/5/2020 = current period

In 4/6/2020 change:

from: 3/6/2020 to: 4/5/2020 - prev_period

from: 4/6/2020 to 5/5/2020 - current

Any suggest? If need more detail just tell me.

[camargos88 · Accepted solution]

@SQUILES ,

I think it needs just an adjustment:

Period =

VAR _startCurrent = EOMONTH(TODAY(); -2) + 6

VAR _endCurrent = EOMONTH(TODAY(); -1) + 5

VAR _startPrevious = EOMONTH(TODAY(); -3) + 6

VAR _endPrevious = EOMONTH(TODAY(); -2) + 5

RETURN

IF('Date'[Date] >= _startCurrent && 'Date'[Date] <= _endCurrent; "Current";

IF('Date'[Date] >= _startPrevious && 'Date'[Date] <= _endPrevious; "Previous"; BLANK()))

Ricardo

[vanessafvg]

can you explain a bit more about how you are using this.   are you wanting a text column called previous period or are you wanting a value for the previous period, some dummy data with an example of what you want to end up with will be really useful.

[SQUILES]

I want a text column called period (current or prev) . Today is 3/31/2020, the range is:

prev = 2/6/2020 - 3/5/2020

curr = 3/6/2020 - 4/5/2020 

When date reach 4/6/2020, the new range

prev = 3/6/2020 - 4/5/2020

curr = 4/6/2020 - 5/5/2020

sample data 

[camargos88]

Hi @SQUILES ,

Try this code for calculated column:

Period =

VAR _startCurrent = EOMONTH(TODAY(); -1) + 6

VAR _endCurrent = EOMONTH(TODAY(); 0) + 5

VAR _startPrevious = EOMONTH(TODAY(); -2) + 6

VAR _endPrevious = EOMONTH(TODAY(); -1) + 5

RETURN

IF('Date'[Date] >= _startCurrent && 'Date'[Date] <= _endCurrent; "Current";

IF('Date'[Date] >= _startPrevious && 'Date'[Date] <= _endPrevious; "Previous"; BLANK()))

Ricardo

[SQUILES]

Thanks @camargos88,

i try something like that using today(), but give me a prev period 4/1/2020 when supose to give me current period.

Current period runs from 3/6/2020 to 4/5/2020.

[camargos88 · Accepted solution]

@SQUILES ,

I think it needs just an adjustment:

Period =

VAR _startCurrent = EOMONTH(TODAY(); -2) + 6

VAR _endCurrent = EOMONTH(TODAY(); -1) + 5

VAR _startPrevious = EOMONTH(TODAY(); -3) + 6

VAR _endPrevious = EOMONTH(TODAY(); -2) + 5

RETURN

IF('Date'[Date] >= _startCurrent && 'Date'[Date] <= _endCurrent; "Current";

IF('Date'[Date] >= _startPrevious && 'Date'[Date] <= _endPrevious; "Previous"; BLANK()))

Ricardo

[SQUILES]

@camargos88 , is work, but i think i can't use today(), to calculated the range, beacuse today()=4/1/2020, and 4/1/2020 is supose to be in current period (current 3/6/2020 - 4/5/2020). 

[camargos88 · Accepted solution]

@SQUILES ,

I just changed the code, try it again:

Period =

VAR _startCurrent = EOMONTH(TODAY(); -2) + 6

VAR _endCurrent = EOMONTH(TODAY(); -1) + 5

VAR _startPrevious = EOMONTH(TODAY(); -3) + 6

VAR _endPrevious = EOMONTH(TODAY(); -2) + 5

RETURN

IF('Date'[Date] >= _startCurrent && 'Date'[Date] <= _endCurrent; "Current";

IF('Date'[Date] >= _startPrevious && 'Date'[Date] <= _endPrevious; "Previous"; BLANK()))

Ricardo