Adding WHERE MAX statement into DAX measure TOTALYTD

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

Namoh  If the goal is to create a running total then you can use this:

 

Create a separate measure for SUM ( Targets[Target Unplanned downtime (hrs)] )

Total Downtime = SUM ( Targets[Target Unplanned downtime (hrs)] )

YTD_Unplanned_Downtine_Target =
VAR MaxVisibleDate =
    MAX ( Dates[Date] )
VAR MaxYear =
    YEAR ( MaxVisibleDate )
VAR DatesLessThanMaxDate =
    FILTER (
        ALL ( Dates ),
        Dates[Date] <= MaxVisibleDate
            && Dates[Calendar Year Number] = MaxYear
    )
VAR Downtime =
    CALCULATE ( [Total Downtime], DatesLessThanMaxDate )
VAR Result =
    IF ( [Total Downtime] <> BLANK (), Downtime )
RETURN
    Result

 

Hi Namoh ,

 

Do you want to calculate the total from the beginning of the year to the maximum date?

 

If yes, maybe you can try this measure.

 

Measure = 
var _lastest_date = MAX('Table (2)'[Date])
return
CALCULATE(SUM('Table'[value]),FILTER(ALLSELECTED('Table'),'Table'[Date]<=_lastest_date))

 

In the sample, the latest date is 2020/7/1, so the total calculates the value of 2020/1/1 – 2020/7/1.

 

 

If it doesn’t meet your requirement, could you please provide a mockup sample based on fake data or describe the fields of each tables and the relations between tables simply?

It will be helpful if you can show us the exact expected result based on the tables.

 

Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.

 

Best regards,

 

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

BTW, pbix as attached.