Matrix Calculate switching negatives too positives

[Jim_F]

Good Afternoon,

Im hopeing someone can tell me what if anything i'm doing wrong here!

I have a table of dates/ week numbers, and in the data set given there are only week numbers. So using the below i'm getting the Week number for last weeka nd the week before based off a TODAY() - 7 & TODAY() - 14 statement. And what I need now is in the matrix to calcualte the difference etween the 2 weeks values.

Now this works to a degree but what i'm seeing is the matrix swapping the values from 2 weeks ago from negatives to positives in the results:

Left is values stright out of the table and the right is the below Measure

Varience 'Last 2 Weeks' =

VAR Week1 = SUMMARIZE(Filter(All(DateLookUp),DateLookUp[Date].[Date] = Today()-7),DateLookUp[WeekNo])

VAR Week2 = SUMMARIZE(Filter(All(DateLookUp),DateLookUp[Date].[Date] = Today()-14),DateLookUp[WeekNo])

RETURN

CALCULATE (

SUM ( 'Last 2 Weeks'[Value (£)] ),

FILTER ( 'Last 2 Weeks', 'Last 2 Weeks'[WeekNo] = Week1 )

)

-

CALCULATE (

SUM ( 'Last 2 Weeks'[Value (£)] ),

FILTER ( 'Last 2 Weeks', 'Last 2 Weeks'[WeekNo] = Week2 )

)

Any tips would be great! Thanks

[Anonymous]

Hi  @Jim_F ,

I created some data:

Last 2 Weeks:

DateLookUp:

Here are the steps you can follow：

1. Create measure.

Flag =
var _weektoday=WEEKNUM(TODAY(),1)
var _week2=_weektoday-2
var _week1=_weektoday-1
var _mindate=MINX(FILTER(ALL('DateLookUp'),'DateLookUp'[Week]=_week2),[Date])
var _maxdate=MaxX(FILTER(ALL('DateLookUp'),'DateLookUp'[Week]=_week1),[Date])
return
CALCULATE(SUM('Last 2 Weeks'[Value (£)]),FILTER(ALL('Last 2 Weeks'),'Last 2 Weeks'[Date]>=_mindate&&'Last 2 Weeks'[Date]<=_maxdate&&'Last 2 Weeks'[Subcategory Level 3]=MAX('Last 2 Weeks'[Subcategory Level 3])&&'Last 2 Weeks'[yearwe]=MAX('DateLookUp'[yearweek])))

Total_Incorrect =
var _table=SUMMARIZE('Last 2 Weeks','Last 2 Weeks'[Subcategory Level 3],"_value",[Flag])
return
IF(HASONEVALUE('Last 2 Weeks'[Subcategory Level 3]),[Flag],SUMX(_table,[_value]))

2. Result:

If I have misunderstood your meaning, please provide your desired output and your pbix without privacy information.

If you need pbix, please click here.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Jim_F]

Hi @Anonymous ,

Thank you for your responce and time modelling this solutions, whilst this does help with retaining of the values.

My goal is to keep the values in the week colums as either negative or positive, and in the total show the variance between the 2 weeks.

From further reading since my first post I believe this may not be possiable, due to the way subtraction works when subtracting a negative from a positive! It may not be mathmatiaclly possiable to get it to work out the varience between both numbers.

Thanks,

Jim