Grouping the date values

[Paramjit]

Hi Team,

Need your help.

I need to group the date values.

I tried using bins but looks like it is gving wrong data.

As below, Start Time is my original column.

It has weekly data (17Apr, 24 Apr, 01May, 08May, 15May, 22May, 29May) but there are multiple entries for same data.   e.g. 17th of Apr has three entires.

But very few cases there is data for other dates as well. e.g. 2nd May and 30 May. 

The requirement is to group the other existing dates to weekly date (means combine the data).

2nd May data needs to be grouped in 1st May and 30th May data needs to group in 29th May.

Firstly i created a bin using 1 days. it is the second column in the screenshot.  it looks working fine as it is grouping the same date by removing the time.

Then i created a bin using 2 days, it is the third column in the screenshot. It is working where the data is highlighted green but is giving incorrect data where data is highlight red. Please see.

 

Thank you,

Paramjit singla

[v-yalanwu-msft]

Hi, @Paramjit ;

If your problem has been solved,  could you please mark the helpful replies as Answered to close this topic and  help the others in the community find the solution easily if they face the same problem with you. Thank you.

[Paramjit]

thank you @Hariharan_R  @SivaMani @ryan_mayu for checking the issue.

I tried the solution given by Hari. (the solution is really out of box and interesting. thank you @Hariharan_R )

It is combining the monthly data with weekly but it is not giving the results as per the requirement.  (It might be because of specifc requireement) e.g. 02May is becoming 08May but requirement is to make it 01May. 

@SivaMani  01May2021 is the weekstart date.  

looks like my issue has been resolved. i used our date dimension table. week start date is giving correct results as per current data set. 

Thank you all.  

[SivaMani]

@Paramjit , Great. Thanks for notifying me.

[ryan_mayu]

@Paramjit 

could you plesae provide the sample data and expected output?

[Paramjit]

@SivaMani  @amitchandak  Any suggestion please. 

let me try to explain here in short.

e.g. For May month, I have 4 rows for weekly data and one row for monthly data.

Monthly data needs to be grouped in weekly data. 

Marix data:

Program name   08 MAY      15 MAY     22MAY    29MAY    30MAY

A                          200             100           500        600          

B                          100             200           100        500

C                          100             300           400        250

D                          100             300           400        250

E                                                                                         100

Desired results:

Program name   08 MAY      15 MAY     22MAY    29MAY

A                         200             100           500        600          

B                         100             200          100         500

C                         100             300           400        250

D                         100             300           400        250

E                                                                           100

[Hariharan_R · Accepted solution]

Hi 

Create one DAX calculated column using below expression. replace datevalue with your date column. This column can be used in your visual.

Data[DateValue]+ 7 - WEEKDAY(Data[DateValue],1)

Thanks

Hari

[SivaMani]

@Paramjit, Try the solution suggested by @Hariharan_R .

You may need to adjust it a bit, What is your week start/end day.

1st May 2021 is Saturday, Is it your start of the week or end?