Adding WHERE MAX statement into DAX measure TOTALYTD

[Namoh]

Hi.

I’ve got a DAX measure which calculates the TOTALYTD: YTD_Unplanned_Downtine_Target = IF( SUM(Targets[Target Unplanned downtime (hrs)]) = BLANK(); BLANK ();TOTALYTD(SUM(Targets[Target Unplanned downtime (hrs)]);'CALENDAR'[Date];ALL('CALENDAR');"3/31"))

This works fine. But when I put this in a table it will show all figures for the whole year, see picture 1.

I would like to only show the results uptill the current month, so I can compare the targets with the actuals, without having to use/change the filter each month.

I’ve made a measure which looks up the latest month, see picture 2: Latest Month = MAX(kpiexport[Month])

 

How to merge both measures into one measure so that the TOTALYTD, for the future months, wont show in my table?

I assume I have to combine the MAX with somekind of WHERE statement?

If another method is possible, I’m also interested in hearing this.

[Namoh]

Yes, you can choose add measure and below is add column, which I thought was the same as adding a measure but then as column so you could see directly the outcome. But I'm a newbie regarding power bi so I could have made a wrong assumption.

So I've created 2 columns via add column in DAX, the option below add measure.

[AntrikshSharma]

@Namoh  Try to create the report like this:

[Namoh]

Well I've created the measures instead as columns and now I don't get an error?

Someone must explain to me what the difference is between these two options, because I expected the same outcome (with the same formula) but apparently with the column option I get an error but with the measure option it works fine...?

So I've created the report but it's not taking into account the FY, starting at april nor does it not show the future months.......

I'm still at the same level as my original post.

[Anonymous]

https://sharepointmike.wordpress.com/2012/12/01/calculated-column-or-calculated-measure/#:~:text=The....

[Namoh]

Thanks, this gives a bit more insight.

Anyone who can help me with my original issue, still not working as expected.

[amitchandak]

@Namoh ,  refer if these can help

example

YTD QTY forced= 
var _max = today()
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),'Date'[Date]<=_max)
//calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))

LYTD QTY forced= 
var _max = date(year(today())-1,month(today()),day(today()))
return
CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
//TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)


YTD QTY forced= 
var _max = maxx('order',[Order date])
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),'Date'[Date]<=_max)
//calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))

LYTD QTY forced= 
var _max1 =maxx('order',[Order date])
var _max = date(year(_max1)-1,month(_max1),day(_max1))
return
CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
//TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)

use date table

https://www.youtube.com/watch?v=Qt0TM-4H09U&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=5