question regarding dates

[bolabuga]

Good day everyone.

I have the following situation:

We do readings of total water (in M³) used in a month, every month.  The readings are made through the hydrometer installed in each house on determined city, and the problem is originated from this scenario, because a "month" though in total varies from 29 to 31 days in general, they usually start for my city, at 09, 10, 11, 12, 13 or 14 of a month and end in the next month in 09, 10, 11, 12, 13 or 14. Let me put a excel table as example.

 

Example table showing that i have readings of hydrometer in the days 12/05/15 and 01/06/2015 and the both dates belong to the reference may/2015 (cycle 5).

Question: Is there a way to tell PBI that he should use my dates(days) respecting the month cycle?? because when comparing dates im getting error telling me that it need to have the same period.

Other thing, im using the following date table, that seens to be working, but if i could use days would be better.

Thks in Advance.

[Anonymous]

Hi @bolabuga,

You can create calculated columns to calculate cycle and cycle year(as same as fiscal year) :

Sample:

Calculate columns:

Cycle = if([Date]>=DATE(YEAR([Date]),MONTH([Date]), 11),MONTH([Date]),if([Date]<DATE(YEAR([Date]),MONTH([Date]), 11),IF(MONTH([Date])>1,MONTH([Date])-1,12),BLANK()))

Cycle Year = if(MONTH([Date])=1&&DAY([Date])<11,YEAR([Date])-1,YEAR([Date]))

Visual:

Regards,

Xiaoxin Sheng

[bolabuga]

Hello xiaoxin, thks for the reply.

I did some writes on paper testing some dates using the calculated column you proposed. What i understand is.

1- The "calculate columns" are always considering that my cycle starts at day 11 and end at day 10, and getting "cycle" and "cycle year" numbers based on that range each month. Did i understand it right??

2- Again considering i understood correctly, theres a problem. Depending on the cycle, it can start at 11 and ending at day 9 or start at day 13 and end at day 11. I will write the actual 2016 cycle dates when i have a time window here on job.

adding new excel sample, sorry i should have added this the first time.

[Anonymous · Accepted solution]

Hi @bolabuga,

>>Did i understand it right??

Yes, it is my option.

>>2- Again considering i understood correctly, theres a problem. Depending on the cycle, it can start at 11 and ending at day 9 or start at day 13 and end at day 11. I will write the actual 2016 cycle dates when i have a time window here on job.

It is hard to calculate the cycle range if you use the random days range to calculate it. I can calculate the cycle if you provide a specific range of days and an start date.

For example: 30 day, start date 2015/1/11.

Measures:

StartDate = DATE(2015,1,11)

DayRange = 30

Current Cycle =
var currentDate=MAX([Date])
var cycleCount=DATEDIFF([StartDate],currentDate,DAY)/[DayRange]
var cycleTotal=INT(cycleCount)+ if(MOD(DATEDIFF([StartDate],currentDate,DAY),[DayRange])>0,1,0)
return
if(currentDate>=[StartDate],if(MOD(cycleTotal,12)>0,cycleTotal-INT(cycleTotal/12)*12,if(MOD(cycleTotal,12)=0,12,cycleTotal)),-1)

Regards,

Xiaoxin Sheng

[bolabuga]

thks xiaoxin, although i cant apply the solutions to my current scenario, it will surely help me along the way. Nice suggestions.