Forum Discussion

AviBI's avatar
AviBI
Frequent Visitor
6 years ago
Solved

Quite frustrated - need help

Hi all 

 

Trying to filter to Last week's items as follows:

 

Period Lookup Table Filters Items Data Table - One to Many

 

LastWkItems = Calculate ([TotalItems] , Period[WeekofYr] =31 , Period[Year] = 2020 )
 
Obviously, I do not want to keep hardcoded values the week number and year (31 & 2020 in this case) to avoid manual intervention every week. So I defined a couple of variable in the Period Lookup Table as follows:
 
LastDate = Lastdate (Period[Date])
 
LastWk = WeekNum (LastDate)
 
Now when I used the variable in the above formula to replace the hardcoded 31, I get the following error:
"A function Calculate has been used a True/False expression that is used as a table filter expression. This is not allowed"
 
Pulling my hair out as to why this is not working when essentially I am replacing a hard number with a variable 
 
Appreciate the help guys 
 
amitchandak , Greg_Deckler parry2k Anonymous 
 
 
 
  • Anonymous's avatar
    Anonymous
    6 years ago

     

    // Of course the one with hard-coded values works.
// I have already explained it in my first post.
// If you read it carefully, you'd know what to do...

[LastWeekItems] =
var __lastWeek = [LastWk]
return
	Calculate (
		[TotalItems],
		WeekofYr = __lastWeek,
		Year = 2020 // this should not be hard-coded
	)

     

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    You are not showing us everything, AviBI. What do you mean by "I defined a couple of variables in the Period Lookup Table"? This makes little sense. You can create variables in a formula, not in a table. Secondly, you can use variables in your measure formula instead of the hard-coded values in exactly the same places but the variables MUST be defined in the measure itself, not outside. Remember that the syntax you're using is syntax sugar since all conditions under CALCULATE must be tables. So, this:

    calculate(
   [measure],
   T[Col] = a
)

    is always translated into:

    calculate(
   [measure],
   filter(
       all( T[Col] ),
       T[Col] = a
   )
)

    "a" must be a value, can't be a measure, for instance.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

       

      // Of course the one with hard-coded values works.
// I have already explained it in my first post.
// If you read it carefully, you'd know what to do...

[LastWeekItems] =
var __lastWeek = [LastWk]
return
	Calculate (
		[TotalItems],
		WeekofYr = __lastWeek,
		Year = 2020 // this should not be hard-coded
	)

       

      • AviBI's avatar
        AviBI
        Frequent Visitor
        6 years ago

        I did read your last post and did not quite understand as I am only used to defining straight forward measures (don't even know how to use "var" etc.)

         

        Also understand that 2020 needs to be treated in a similar way as the week numbers. so I'll need to follow the same approach for the year as well

         

        Thanks a lot for your help. I will now try this 

         

         

         

    • AviBI's avatar
      AviBI
      Frequent Visitor
      6 years ago

       

      This is the end result I am after - but I have acheived this using manual filters and I do not want to update the filters every week. This should be automated. 

       

       

      Common field between Calendar & Data (Items Table) is "Period"

       

      Measures:

       

      TotalItems = Calculate (Sum ('Items&Serv'[Items]), 'Items&Serv'[ItemType] = "Items")
      ItemType column comprises 5 different categories but I am only interested in "Items" and hence I have used the measure to isolate the total for these
       
      I am only interested in last weeks Items (week 31 of the year). The week numbers are available in the Calendar as a custom power query custom column
       
      So I defined two measures:
       
      LastDate = Lastdate (Calendar[Date]) - this pulls the last date from the data table (Calendar table references the Items Table) in Power Query
       
      LastWk = WeekNUM (LastDate) - This takes the LastDate measure above and calculates the Dax WeekNum 
       
      So far so good
       
       
       

      This is exactly what I need. 

       

      Now in order to get to Last weeks items through a measure approach (not manual filters), this is how I think the measure should look like:

       

       

      LastWeekItems = Calculate (TotalItems , WeekofYr = LastWk , Year = 2020)

       

      This does not work. "Calculate" can not be used with True/False error

       

      This however works:

       

      LastWeekItems = Calculate (TotalItems, WeekofYr = 31, Year = 2020)

       

       

       

      Now you may tell me that I am going about it all wrong which is fine. I just need the report to pickup the last week, and display the items on a daily basis (as shown above) and I need this to happen automatically based on the data and relationships (as above)

       

      Hope this gives all the info you required to help

       

      amitchandak Anonymous Greg_Deckler