Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Chamalpam
Frequent Visitor

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.
1 ACCEPTED SOLUTION
Arul
Super User
Super User

@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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!


LinkedIn


View solution in original post

2 REPLIES 2
Arul
Super User
Super User

@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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!


LinkedIn


Thank you so much Arul! This works!!!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.