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

Help with a formula to use a different, but "equal" value.

Currently I have a report based a weekly Time frame. The week ends on Sundays. 

I have a table linked into the primary table, with a many to one relationship (Many Primary Table side, one Support table side) 

The support table has two columns, one column start with 1 and going to X (Static value) the second column being the first week in the data set, and the last being the last value in the data set. 

Below is the formula I am currently using to calculate a four week average. 

--------------------

Var wk_1 = IF((MAX('Week Count'[Week Number])-3) < 0, 0, (MAX('Week Count'[Week Number])-3))
Var wk_2 = IF((MAX('Week Count'[Week Number])-2) < 0, 0, (MAX('Week Count'[Week Number])-2))
Var wk_3 = IF((MAX('Week Count'[Week Number])-1) < 0, 0, (MAX('Week Count'[Week Number])-1))
Var wk_4 = IF((MAX('Week Count'[Week Number])) < 0, 0, (MAX('Week Count'[Week Number])))

Var RollAVG = CALCULATE(DISTINCTCOUNT('Week Count'[Week Number]) ,'Week Count'[Week Number]in {wk_1, wk_2, wk_3, wk_4})

Return CALCULATE([WeeklyTotals], 'Week Count'[Week Number] in {wk_1, wk_2, wk_3, wk_4})/RollAVG
--------------------
Currently, with the above formula, I can not get my bar chart to work with anything but the [Week Number] Column, even though it is all in a 1 to 1 relation ship. 

Any suggestions on how I can get this to function with a date, or even a date mascarding as text to get around the auto date handling in powerbi? 
1 ACCEPTED SOLUTION
Anonymous
Not applicable

I managed to resolve the issue by creating a date table. 

View solution in original post

3 REPLIES 3
v-cazheng-msft
Community Support
Community Support

Hi, @Anonymous 

Is your problem solved? Can you share some sample data removing sensitive data if your problem hasn't been solved?

 

Best Regards 

Caiyun Zheng

Anonymous
Not applicable

I managed to resolve the issue by creating a date table. 

parry2k
Super User
Super User

@Anonymous it will be easier if you share sample data and expected output. Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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.