cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Anonymous
Not applicable

## DAX/M Query

Hi Team,

Good Morning!

I need Sumif calculation for YDT process insert into summary table, see the below screenshot,

Summary Table- need to add "YTD-20" column for few months sum calculation.

Thanks,

KVB

1 ACCEPTED SOLUTION
Community Support

Hi, @Anonymous

Table:

You may create a measure as below.

``````Result =
CALCULATE(
SUM('Table'[Value]),
FILTER(
ALL('Table'),
'Table'[Date]<=MAX('Table'[Date])&&
'Table'[GHT] = SELECTEDVALUE('Table'[GHT])
)
)``````

Then you may use a matrix visual to diplay the result.

Best Regards

Allan

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

3 REPLIES 3
Community Support

Hi, @Anonymous

Table:

You may create a measure as below.

``````Result =
CALCULATE(
SUM('Table'[Value]),
FILTER(
ALL('Table'),
'Table'[Date]<=MAX('Table'[Date])&&
'Table'[GHT] = SELECTEDVALUE('Table'[GHT])
)
)``````

Then you may use a matrix visual to diplay the result.

Best Regards

Allan

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

Super User

@Anonymous , not very clear, but you can get YTD wit date table

``````YTD QTY = TOTALYTD(Sum('order'[Qty]),'Date'[Date])
Previous Year = CALCULATE(SUM('order'[Qty]), PREVIOUSYEAR('Date'[Date]))

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

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
``````

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184

Anonymous
Not applicable

I need (Feb to Nov month) sum of value.

Thanks,

KVB