Forum Discussion

Chamalpam's avatar
Chamalpam
Frequent Visitor
3 years ago
Solved

Need Help with PowerBI variables

Hey Everyone,

 

I am new here and i am wondering whether i can use a date as a variable in a messure.

 

I tried using below but i am getting an error saying "A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

 
24Months =
var EndDate = (DATE(2023,01,31))
var One_Month = CALCULATE([Total_FG],Date_Table[Date]<=([EndDate]))
Return
One_Month
 
I tried using the same without the variable and below works
24Months = CALCULATE([Total_FG],Date_Table[Date]<=DATE(2023,01,31))
 
End goal  is to calculate the same figure, off setting the month by 1 for 24 months.
Need Help
  • Arul's avatar
    Arul
    3 years ago

    Chamalpam ,

    try this

    24Months = 
VAR EndDate = DATE(2023, 1, 31)
VAR StartDate = DATE(YEAR(EndDate) - 2, MONTH(EndDate), DAY(EndDate))
RETURN
CALCULATE([Total_FG], Date_Table[Date] >= StartDate, Date_Table[Date] <= EndDate, DATEADD(Date_Table[Date], 1, MONTH))

    Thanks,

    Arul

2 Replies

  • Arul's avatar
    Arul
    Super User
    3 years ago

    Chamalpam ,

    try this

    24Months = 
VAR EndDate = DATE(2023, 1, 31)
VAR StartDate = DATE(YEAR(EndDate) - 2, MONTH(EndDate), DAY(EndDate))
RETURN
CALCULATE([Total_FG], Date_Table[Date] >= StartDate, Date_Table[Date] <= EndDate, DATEADD(Date_Table[Date], 1, MONTH))

    Thanks,

    Arul