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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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 )

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors