Forum Discussion

MarieKatherineJ's avatar
MarieKatherineJ
Frequent Visitor
3 years ago

Calculate Sum Filter with a Circular Dependancy error

Hi community !
I am new with DAX and I would like to create 3 calculated columns with multiple criterias.
 
First Column : Sales of this week
The first criteria is the fiscalweek : the actual weeknum according to the normal calendar is 11. However, in a certain company, their fiscal year start July 1st. So right now we are week 37. 
So I need to sum my value for week 37.
The second criterias is the fiscalyear : In order to not sum the values of every week number 37 of every year, I need to add the second criteria of the fiscal year.
 
Second Column : Sales of Week-1 (aka this week - 7 days)
"" Same criterias as above
 
Third Column : Sales of Week-2 (aka this week - 14 days)
"" Same criterias as above
 
I have a reference table named "FiscalDates" with a list of every date and their equivalent of fiscalweek and fiscalyear for the company.
 
So I created this function and it works perfectly for the First Column.
 
SalesThisWeek = calculate(
sum('public VentesProduitsStoreParJour'[QTYVENDU]),
--first criteria : fiscalweek must equal this week
FILTER('public FiscalDates',
('public FiscalDates'[TRANSDATE].[Date]=TODAY() &&  'public VentesProduitsStoreParJour'[FISCALWEEK]='public FiscalDates'[FISCALWEEK])),
--second criteria : : fiscalyear must equal this year
FILTER('public FiscalDates',
('public FiscalDates'[TRANSDATE].[Date]=TODAY() &&  'public VentesProduitsStoreParJour'[FISCALYEAR]='public FiscalDates'[FISCALYEAR]))
)



The problem is when I want to create my second column and my third column, I have a Circular Reference error message. 

I used the same syntax as the function of the first column : 

 

SalesWeek-1 = calculate(
sum('public VentesProduitsStoreParJour'[QTYVENDU]),
--first criteria : fiscalweek must equal weeknumber of last week 
FILTER('public FiscalDates',
('public FiscalDates'[TRANSDATE].[Date]=TODAY()-7 &&  'public VentesProduitsStoreParJour'[FISCALWEEK]='public FiscalDates'[FISCALWEEK])),
--second criteria : fiscalyear must equal yearnumber of last week
FILTER('public FiscalDates',
('public FiscalDates'[TRANSDATE].[Date]=TODAY()-7 &&  'public VentesProduitsStoreParJour'[FISCALYEAR]='public FiscalDates'[FISCALYEAR]))
)
 
Can you please help me.🙏
 
I have read some suggestion about using ALLEXCEPT in the filter function but I can't make it work. 
 
Thank you very much !!
 
Need Help

3 Replies

  • andhiii079845's avatar
    andhiii079845
    Icon for Solution Sage rankSolution Sage
    3 years ago
    You can improve this: 
     
    SalesThisWeek = calculate(
    sum('public VentesProduitsStoreParJour'[QTYVENDU]),
    --first criteria : fiscalweek must equal this week
    FILTER('public FiscalDates',
    ('public FiscalDates'[TRANSDATE].[Date]=TODAY() &&  'public VentesProduitsStoreParJour'[FISCALWEEK]='public FiscalDates'[FISCALWEEK])),
    --second criteria : : fiscalyear must equal this year
    FILTER('public FiscalDates',
    ('public FiscalDates'[TRANSDATE].[Date]=TODAY() &&  'public VentesProduitsStoreParJour'[FISCALYEAR]='public FiscalDates'[FISCALYEAR]))
    )
     
    You make it i think to complicated. I think you are able to access directly the right fiscalweek via "relatedtable"
    Like this:

     

    Salesthisweek =
    VAR _date= TODAY()
    VAR _fiscal = CALCULATE(max(Dim[Fiscalweek]),FILTER(Dim,Dim[Date]=_date))
    RETURN CALCULATE(sum('Table'[Sale]),FILTER(RELATEDTABLE(Dim),Dim[Fiscalweek]=_fiscal))
     
    Try for the other measure
    Saleslastweek =
    VAR _date= TODAY()-7
    VAR _fiscal = CALCULATE(max(Dim[Fiscalweek]),FILTER(Dim,Dim[Date]=_date))
    RETURN CALCULATE(sum('Table'[Sale]),FILTER(RELATEDTABLE(Dim),Dim[Fiscalweek]=_fiscal))
     
    Nevertheless, can you please share some data examples for the both tables? 
     
    • MarieKatherineJ's avatar
      MarieKatherineJ
      Frequent Visitor
      3 years ago

      Hello ! 
      Thank you for your reply and sorry for taking so long.

      I tried reproducing your function but when I type "Var_ " it doesn't exist. It proposes to me : "Var.P , Var.S, Varx.P or Varx.S.

      Is there something I did not understand ? 
      Thank you !

      • andhiii079845's avatar
        andhiii079845
        Icon for Solution Sage rankSolution Sage
        3 years ago

        It called VAR "name" like:

        my measure =
        VAR _test = 1

        VAR result = 2

        Return result

         

        you write VAR_