Forum Discussion
Grouping the date values
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
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)ThanksHari
7 Replies
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
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)ThanksHariParamjit, 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?
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.
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.
