topic Previous dates using date add and switch in DAX Commands and Tips

Previous dates using date add and switch

Lenastray — Mon, 06 Jul 2020 16:11:28 GMT

Hi,

Thanks in advance for the support. The objective is to make a measure that gives the previous aummount for any date, excluding holidays and weekends. I was using the "is holiday" and "is weekend" from the model calendar table to switch between diferents dateadd for some aggregation measure, but it gave error as result.

measure := calculate(aggregateMeasure,

switch(TRUE(),

calendar[isHoliday] = 1 and calendar[day] = 5, dateadd(calendar[date], - 4, day),

... //for every use case

)

but the result is error : /

thanks for any help

Re: Previous dates using date add and switch

amitchandak — Mon, 06 Jul 2020 17:07:53 GMT

Lenastray , Not very clear. Check if this can help

https://community.powerbi.com/t5/Community-Blog/Travelling-Across-Workdays-Decoding-Date-and-Calendar-4-5-Power/ba-p/1187766

Re: Previous dates using date add and switch

Anonymous — Tue, 07 Jul 2020 08:55:55 GMT

Of course you get an error because SWITCH cannot return a table and tables and tables only can be filters in CALCULATE. Also, calling field names without wrapping them into an aggregation function when there's no row context must lead to errors.

Please try to read something about CALCULATE and how filters work in DAX.

By the way, your case is not clear at all. Please explain in detail what you want to do and then someone will certainly be able to help you.

Best
D