Calculated Columns to get dates for 2 weeks and 1 month

[Anonymous]

I have an issue, how to get dates that falls within 2 weeks and 1 month.

For 2 weeks, it is from today to next 2 weeks and the day starts from Monday to Sunday.

For 1 month, it is from today to next 1 month.

How can i create this as a calculated column and How can i use it with my measure?

I want to create a botton for two weeks and 1 month so it can filter through the measures 

Thanks

[Anonymous]

Can someone help please? I want to countID that falls between today's date and next 2 weeks date and similar count of ID that falls between today's date and 1 month. My monday to sunday is my week start and week end 

[v-easonf-msft]

Hi, @Anonymous 

Not fully sure what you want. Can you explain your needs concretely with the following example?

If today is May 1st, on what day should the 'next 2 weeks' or '1month' start and end?

Best Regards,
Community Support Team _ Eason

[Anonymous]

@v-easonf-msft if today is 1st of May 2022, then start date will be 01/05/2022 and enddate is 14/05/2022 for next 2 weeks. For 1 month startdate is 01/05/2022 and enddate is 28/05/2022 (28 days).

Furthermore, if I want to count milestone for the next 2 weeks, it should be the count of milestone that falls between today and next 14days. similar to 1 month

[v-easonf-msft · Accepted solution]

Hi， @Anonymous 

Maybe I think too much. Actually, it has nothing to do with the day of the week. what you want is a count for the next 14 days/ 28 days, right?

Please try formula as below:

Count in next 2 weeks = CALCULATE(COUNT('Table'[ID]),FILTER('Table','Table'[Date]>=TODAY()&&'Table'[Date]<=TODAY()+14))

Count in next 1 month = CALCULATE(COUNT('Table'[ID]),FILTER('Table','Table'[Date]>=TODAY()&&'Table'[Date]<=TODAY()+28))

If I misunderstood, please upload your sample files to cloud storage and share the link here.

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , do you need a 4 4 4 calendar?

example

Cal 444 = var _cal = CALENDAR(Date(2018,01,01), Date(2022,12,31))
return ADDCOLUMNS( _cal
,"Year" , QUOTIENT( DATEDIFF(Minx(_cal, [Date]), [Date], day) ,364)+1
,"Day Of Year" , Mod( DATEDIFF(Minx(_cal, [Date]), [Date], day) ,364)+1
,"Month" , Mod(QUOTIENT( DATEDIFF(Minx(_cal, [Date]), [Date], day) ,28),13)+1
,"Year Month" , (QUOTIENT( DATEDIFF(Minx(_cal, [Date]), [Date], day) ,364)+1)*100 + Mod(QUOTIENT( DATEDIFF(Minx(_cal, [Date]), [Date], day) ,28),13)+1
,"Week" , Mod(QUOTIENT( DATEDIFF(Minx(_cal, [Date]), [Date], day) ,7),52)+1
)

[Anonymous]

@amitchandak Nope. I don't have 4 4 4 calendar. I have a date table and would like to create 2 weeks and 1 month calculated column and use it with measures as a botton or bookmark

[amitchandak]

@Anonymous ,Can you share an example data

If the month is of 4 Weeks you can get the code above

For 2 week from Today

Week Start date = today() +-1*WEEKDAY( today(),2)+1

Week End date = today() +-1*WEEKDAY( today(),2)+1 +13

[Anonymous]

I don't know how to share. my day of the week starts on Monday. how can i do month