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

[Anonymous]

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? 

[v-cazheng-msft]

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 · Accepted solution]

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

[parry2k]

@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