cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Anonymous
Not applicable

## Need HELP with DAX please!

Hello All

I have a complex request to be able to select a sample of data for auditing based on user-selected dates.

What I want to be able to do is:

1. Take the Date diff between the User-selected date and “Referral date”
2. Calculates the weeks based on the step1. I.e. Total Days/7
3. I need to Group step 2 to create a Week Range. So if the week between 0 && 1 then ‘0-1 Weeks’, Weeks between 1 && 2 then ‘’1-2 Weeks etc..

I am particularly struggling around Step 3, which I am trying to do it in a column as I would need this in to show the total customers by Weeks Range a clustered chart.

You can see above that Total Days from the selected date is working which is taking the date diff from the clockstartdate and User-selected date. In Weeks = Total Days/7.

Is it possible to create a Column that’s based on the “In weeks” measure to show this is ‘0-1 Weeks’ range ?

Any help would be much appreciated.

1 ACCEPTED SOLUTION
Community Support

Hi, @Anonymous

This can be a little difficult if your model involves slicers.
You are using a calculated column instead of a measure, which will cause the result to be static(not recalculated based on your current filter context). If you use measure instead, the result will be correct, but you won't be able to apply it to the 'Axis' on the bar chart.

Best Regards,
Community Support Team _ Eason

9 REPLIES 9
Anonymous
Not applicable

Any help would be much appreciated guys! thank you

Anonymous
Not applicable

@tackytechtom I tried to replicate your logic in a Column but it showing me the wrong range, especially when you have -ve In Weeks figures - see below. Are you able to share your PBIX file please? @tackytechtom

Anonymous
Not applicable

@amitchandak Any luck?

Anonymous
Not applicable

@amitchandak thank you for your quick response. So your suggestion works fine in Measure. Is it possible to Group the Measure week into a range? For example say if the Measure week is returning 0.43, then Weeks Range as 0-1 Weeks?

I need this in a column so that I can create a clustered Chart, total customers by Week Range - something like below.

Super User

Hi @Anonymous ,

I have a first draft here:

The question is though what to do when it exactly hits a week (or a multiple of that, see 7 in picture). Should it then show 6-7 Weeks or 7-8 Weeks? You might be able to tweak the DAX below accordingly, but this might be a start for you:

```TomsGroupingWeekTable =
ROUNDDOWN( Table[In Weeks], 0 ) & "-" & ROUNDUP( Table[In Weeks], 0 ) & " Weeks"```

Let me know if you need further assistance!

/Tom

https://www.tackytech.blog

https://www.instagram.com/tackytechtom

 Also happily accepting Kudos 🙂

 Feel free to connect with me on LinkedIn!

 #proudtobeasuperuser

Anonymous
Not applicable

@tackytechtom Well actually, I added a IF statement to deal with the -ve In Weeks figures but it didnt go well for other In Weeks figures.

You can see below, User selected date is 31/07/2021, based on that you can see there should be a range from 0-1 Weeks, 1-2 Weeks, and 2-3 Weeks etc.. but its shows as 0-1 Weeks for all.

This is my Dax code

@tackytechtom, not sure if I am missing something obvious, any help would be much appreciated.

Many thanks

Community Support

Hi, @Anonymous

This can be a little difficult if your model involves slicers.
You are using a calculated column instead of a measure, which will cause the result to be static(not recalculated based on your current filter context). If you use measure instead, the result will be correct, but you won't be able to apply it to the 'Axis' on the bar chart.

Best Regards,
Community Support Team _ Eason

Anonymous
Not applicable

Thank you @v-easonf-msft .I suspect this might be the case. Would be good to enable this feature in the Power BI.

Super User

@Anonymous , Try measure like

Measure =

var _min = minx(allselected(Date),Date[Date])

return

Averagex(Table, datediff(Table[clock Start date], _min, day) )

Measure Week=

var _min = minx(allselected(Date),Date[Date])

return

Averagex(Table, datediff(Table[clock Start date], _min, day)/7 )

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors