topic Re: Apply time period to IF measure in DAX Commands and Tips

Apply time period to IF measure

KMadsen — Thu, 06 Oct 2022 06:46:30 GMT

Hi All,

I have an exisitng measure that works well - it is based on a number of "IF" statement and in the end calculates the accuracy of a forecast.

Forecast accuracy Consensus = if(sum('Region consolidated data'[Total demand])=0 && sum('Region consolidated data'[ABS deviation consensus])<>0,0,if(1-DIVIDE(

SUM('Region consolidated data'[ABS deviation consensus]),

SUM('Region consolidated data'[Total demand])

)<0,0,1-divide(sum('Region consolidated data'[ABS deviation consensus]),sum('Region consolidated data'[Total demand]))))

I can filter the data to only show the last 3 months, 6 months etc. through the date slicer.

However, I would also like to have a "static" measure for the last 3 month, 6 months and 12 months to be able to show these side by side in a table.

Can anyone advise on how I can create "new" measures based on above but limited to the mentioned time periods?

Thanks!

Re: Apply time period to IF measure

johnt75 — Thu, 06 Oct 2022 09:59:06 GMT

You could create a calculation group with calculation items for each period you want to show, e.g.

3 months = VAR StartDate = EOMONTH ( TODAY (), -4 ) + 1 RETURN CALCULATE ( SELECTEDMEASURE (), 'Date'[Date] >= StartDate && 'Date'[Date] <= TODAY () )

Re: Apply time period to IF measure

daXtreme — Thu, 06 Oct 2022 10:50:51 GMT

You should ditch your IF's in favour of SWITCH. Second, creating a 'static' measure is simple - use the right filters on your existing measures.