Expression Help - Date

[Anonymous]

Hi All,

I need some help to excluded dates for my sum function. Currently, I use this formula to exclude name where =”Rick”. Sum({<[Type]={'Cost'},Name-={'Rick'}>}[Amount])”. Instead of the Name field I want to use a “Date” field.

What I want to do is exclude dates where it happen during the quarter. In my dashboard, I have “Reporting Date” field such as 3/31/2017, 6/30/2017, and 9/30/2017. For example: if I selected Reporting Date field 6/30/2017,  I want the formula to exclude “Date” where Date >= 4/1/2017   and Date <=6/30/2017.

Sum({<[Type]={'Cost'},Date >= 4/1/2017   and Date <=6/30/2017>}[Amount])

Any input greatly appreciated! 

Many Thanks,

Frank

[shraddha_g]

Sum({<[Type]={'Cost'},Date >= 4/1/2017   and Date <=6/30/2017>}[Amount])

If here 4/1/2017 is your year start of selected date (6/30/2017) then,

sum({<[Type] = {'Cost'}, Date = {">=$(=Date(YearStart(Max(Date),0,4),'M/D/YYYY'))<=$(=Date(Max(Date),'M/D/YYYY'))"}>}[Amount])

[Anil_Babu_Samineni]

In fact, Dates seems static. Would you go same then simply to call. If you notice below statement you could get some minus symbol before equal sign.

Sum({<[Type]={'Cost'},Name-={'Rick'}, Date -= {">=4/1/2017 <=6/30/2017"}>}[Amount])

OR

Sum({<[Type]={'Cost'},Name-={'Rick'}, Date -= {"=Date >= 4/1/2017 and Date <= 6/30/2017"}>}[Amount])

Note - Make sure to clear what you need if dynamic needed.

[pradosh_thakur · Accepted solution]

try this

sum({<[Type] = {'Cost'}, Date -= {">=$(=Date(quarterstart(Max(Date)),'MM/DD/YYYY')) <=$(=Date(Max(Date),'MM/DD/YYYY'))"}>}[Amount])

[Anonymous]

All,

Thanks for quick responses. Will check it out later.