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
tameismael
Helper I
Helper I

Automatic calculation

Dear Dax community,

 

hope you are all doing well !!

 

I do have have this question please: 

we do have contacts to reach out in order to provide specific service in our company, what's requested here is to calculate the number contacts reached at the end of each week. What I'm doing now manually as you see in the below screenshot  *2 (up to week 2) at the end of each week , or  *3 (up to week 3) , *4(up to week4) ,etc.. until reaching week 12. As you see the value is accumulating by week so now it's 76 as shown in the attached image means this value for 2 weeks only.

 

I'm seeking a way that powerbi can do this calculation automatically without me multiplying each week by the number of finished week 2,3,...12 weeks, and the number should be presented on a Gauge as shown in the below screenshot.

 

any help please urgently,

thanks 

01F3BB17-C907-4352-B5F9-2D3D5A05A333.jpeg

5A0C96AB-3AA9-4D8C-B8CA-6D380D947409.jpeg

2 ACCEPTED SOLUTIONS

Hi Tameismael,

Following this instruction:

1. You can create a new parameter from Mordeling /New parameter. 
2. Use data type with whole number, minimum = 0, maximum = the number week you expect to see.

3. Then multiply your current formula with that parameter.
4. Create a slicer or filter above with that parameter

You can download the sample file here for your case
https://drive.google.com/drive/folders/1qZh8MtEuSvm9F-kERIX3AZ12wxWxpFoU?usp=sharing

View solution in original post

Yes, if your data have a date column for each record, so you can use this formula to calculate the week up to today, Then you can model the relationship between this new column with Parameter above

New column in your data table:
WeekNoUpToToday = WEEKNUM(TODAY()) - WEEKNUM(DateOfRecord)

View solution in original post

8 REPLIES 8
tameismael
Helper I
Helper I

any other responses please members/users

 

thanks

linh091
Resolver I
Resolver I

If I understand you correctly, you want the number of weeks to be changed automatically as the user's expectation when using it (without changing the formula).
I think you can create another table with the rows containing the numbers 1, 2, 3, 4... Then you can create a filter with the button 1 - 2 -3 - 4... after making the relationship between the tables

Filter.PNG



Can you please provide a real example on that so the idea will be clear.

thank you

Hi Tameismael,

Following this instruction:

1. You can create a new parameter from Mordeling /New parameter. 
2. Use data type with whole number, minimum = 0, maximum = the number week you expect to see.

3. Then multiply your current formula with that parameter.
4. Create a slicer or filter above with that parameter

You can download the sample file here for your case
https://drive.google.com/drive/folders/1qZh8MtEuSvm9F-kERIX3AZ12wxWxpFoU?usp=sharing

thank you again I have done it and it's successful. But is there is a way to add to the formula the Today function so it will bring my the number of contacts reached up to date ??

 

thanks

Yes, if your data have a date column for each record, so you can use this formula to calculate the week up to today, Then you can model the relationship between this new column with Parameter above

New column in your data table:
WeekNoUpToToday = WEEKNUM(TODAY()) - WEEKNUM(DateOfRecord)

Thank you very much I do appreciate that , I will be doing those steps.

 

many thanks,

Thank you indeed, I will try it and see if it gives me the same results I'm looking for I'm not sure yet. 

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