Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
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.

 

 

Sherin89_0-1644412939509.png

 

 

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

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

View solution in original post

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 

 

Sherin89_0-1644485426332.png

 

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.

 

Sherin89_0-1644413636426.png

 

 

Hi @Anonymous ,

 

I have a first draft here:

tomfox_0-1644443682257.png

 

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

 

 

 

 

 

 



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! 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. 

 

Sherin89_1-1644485963867.png

 

This is my Dax code

 

Sherin89_2-1644486214027.png

 

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

 

Many thanks

 

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. 

amitchandak
Super User
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 )

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.